Forum Discussion

JollyRoger01's avatar
JollyRoger01
Icon for Helper III rankHelper III
5 years ago
Solved

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?

Need Help
  • AlB's avatar
    AlB
    5 years ago

    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"

     

     

    		 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.

     

1 Reply

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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"

     

     

    		 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.