Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
I want to combine tables so that I get all possible combinations from each table, like so:
Input table 1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PM1SK1YGxjZDYxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Resource", type text}})
in
#"Changed Type"
Input table 2
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCq4sLknNNVSK1YGxjZDYxkhsEyS2KRLbDIltrhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Systems = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Systems", type text}})
in
#"Changed Type"
Output table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcwrDoAwEEDBu6yu6X7aa5Agm8pKIKEYbg+GYJ4dMa3JMs557FmSrPe8xpalp08V1VD/QXFQHBQHw8FwMBwcB8fBcQgcAofAoeBQcCg4VBwqDq/2Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, System = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Resource", type text}, {"System", type text}})
in
#"Changed Type"
Could someone please show me what I need to do to combine each table so I get that output?
Solved! Go to Solution.
Place the following M code in a blank query to see the steps (additional steps on your InputTable1). It reads the Systems column from InputTable2 and expands it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PM1SK1YGxjZDYxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Resource", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "System", each InputTable2[Systems]),
#"Expanded System" = Table.ExpandListColumn(#"Added Custom", "System"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded System",{{"System", type text}})
in
#"Changed Type1"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Place the following M code in a blank query to see the steps (additional steps on your InputTable1). It reads the Systems column from InputTable2 and expands it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PM1SK1YGxjZDYxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Resource", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "System", each InputTable2[Systems]),
#"Expanded System" = Table.ExpandListColumn(#"Added Custom", "System"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded System",{{"System", type text}})
in
#"Changed Type1"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.