Splitting a 14 column table into two tables?

Hi there,

I have a 14 column table which starts off at 88,000 rows. I then filter it for rows relevant to me and it drops to 12,000. As I second step I would like to “extract” out 4 columns to make a second table. The remaining table then having only 10 columns. Should I change the filter in the primary table (10 columns), it must “ripple through” to the secondary table (4 columns). Which means it can’t just be a duplication, and it can’t just be another source connection.

I have struggled with referencing as I do not want duplication of columns in the two tables. If I delete a column in the referenced table, then it causes problems in the latter table.

Anyone got any ideas?

Thanks in advance,

Hello @michellepace,

Thank You for posting your query onto the Forum.

Without looking at the data it always been a difficult to judge the result and provide the solution that you’re looking for. Please provide the working of your file. If the data is sensitive then please create a mock-up data. I’m providing a link of a video which was created by one of expert @BrianJ about “How to mask the sensitive data data”. Also you can through the course “Advanced Data Transformations and Modelling” which is available on our education portal.

Thanks and Warm Regards,

Hi @michellepace. As @Harsh said, a PBIX is always best. Just to give my two cents, I’ve found that importing your dataset as an unchanged staging table, then referencing it and removing columns as necessary in each reference works quite well; if your filtering applies before the referencing, you should be able to filter your staging table and the “references” will thus be created from the filtered staging table. Should not be a problem to get this to work, but a PBIX will help greatly. Greg

1 Like

Hi Greg, thank you. The staging ideas seems to fit all my needs. Although truth be told I really don’t like the duplication of data. I guess you’ve just got to be satisfied with a solution which is “good enough” rather than absolutely perfect. Thanks very much, filtered staging table it is with two referencing tables created off of that.

@michellepace make sure to turn off ‘enable load’ on the staging table, this will cut down on the size impact to your final model.

Also, referencing does not load the data multiple times; data is loaded once and used multiple times …

Thank you very much @Heather and @Greg. Both of your comments are very helpful.

1 Like