Split a table in Power Query

[SeanMcN]

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).

[SeanMcN]

Thanks, @lbendlin -- but perhaps I was unclear.
I don't mind creating the daughter tables manually in Power Query; I'm not looking for a dynamic solution.

I'm hoping that I can use the "master" table to create several well structured daughter tables in Power Query.

[lbendlin · Accepted solution]

You can reference queries - also check the "Query Dependencies" lineage view.  Power Query may still decide to do some of the work twice when it suits its execution plans.

How Power Query Thinks: Taking the Mystery Out of Streaming and Query Folding (Video) | Ben Gribaudo

[SeanMcN]

Thanks again, @lbendlin -- that helps greatly!

🙂

[lbendlin]

It is impossible (in the Power Query UI) to dynamically create queries.  All partitions must be manually created.