I'm trying to use PowerBI to recreate a data flow that I originally created in Tableau Prep and Tableau Desktop.
I need to find a way to split a single large data table into several smaller tables using Power Query. I know I can create calculated tables using DAX, but several of the daughter tables also need to have columns unpivoted, and my ideal solution would handle the data processing in Power Query...
Here's our summary workflow:
- Receive two separate but structurally identical Excel files from our research partners.
- Add a calculated column to each input table (the column has the same name but different values in each table)
- Aggregate the two input tables into one "master" table
At this point, I would like to use the "master" table to create several smaller daughter tables, all related by a common unique ID:
- A table with demographic information
- A table with screening question responses
- Several other daughter tables, with specific columns unpivoted
Then I'd related all of the daughter tables using the unique ID and analyize in PBI
Right now, it looks like the only way to do this in Power Query is to execute multiple queries, all based on the two original Excel files.
If that's the answer, that's fine -- but I'd prefer to not perform the same tasks repeatedly (such as deleting columns that are irrelevant to all downstream tables).
