Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a table in a dataflow in Power Query - Called the Master Data
Example Above
I want to be able to dynamically split this table based on the names of this fake data:
Each name will get their own table dynamically created based on the Master Data;
For example:
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"
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.
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