Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Oddy
Frequent Visitor

Power Query - Dynamically Split a Table based on a column into multiple Child Tables

I have a table in a dataflow in Power Query - Called the Master Data

 

Oddy_0-1713343293903.png

Example Above

 

I want to be able to dynamically split this table based on the names of this fake data:

Oddy_1-1713343341995.png

Each name will get their own table dynamically created based on the Master Data;

For example:

Oddy_2-1713343432471.png



The reason that it needs to be dynamically made rather than manually made is simply because there are over 60 tables that will change over time, they'll increase and decrease and I want to store different peoples' information in a Data Warehouse for analysts to be able to just select who they want and move on - and I don't want them to be able to select the master data. 

 

Is this achievable? 



e/ Json snippet of the master data for those who want to give something a go:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3MTlXSUQpPLEnOANKGBgZKsTpwYef8tLTUVAXn0gIgxwgqF5SfnloE5Adk5OeBFBmaQsSDMxKL8vOQJYxQDAPboWCETYd3Zl5KDliLCUTGMTc1JzMRKOCbX1oMljADSsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Product = _t, Spend = _t]),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Name", type text}, {"Product", type text}, {"Spend", Int64.Type}})
in
  #"Changed column type"

 

1 REPLY 1
dufoq3
Super User
Super User

Hi @Oddy, power query can't create new query for you - it can just update existing query (for example it can create separate table for every name but only inside same query - see code below). But to be honest - why do you want to create new separate table for every name when you can just filter existing master table.

 

dufoq3_0-1713358265155.png

 

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3MTlXSUQpPLEnOANKGBgZKsTpwYef8tLTUVAXn0gIgxwgqF5SfnloE5Adk5OeBFBmaQsSDMxKL8vOQJYxQDAPboWCETYd3Zl5KDliLCUTGMTc1JzMRKOCbX1oMljADSsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Product = _t, Spend = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Spend", type number}}),
    CreateSeparateTables = List.Transform(List.Distinct(ChangedType[Name]), (x)=> Table.SelectRows(ChangedType, each [Name] = x))
in
    CreateSeparateTables

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.