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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
JollyRoger01
Helper III
Helper III

How to merge tables and repeat each values for each row

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?

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

Hi @JollyRoger01 

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"

 

 

SU18_powerbi_badge

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.

 

View solution in original post

1 REPLY 1
AlB
Community Champion
Community Champion

Hi @JollyRoger01 

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"

 

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors