Nested column to new table

[LeviCaers]

Hi,

I want to create a new table that combines two columns from different tables.
Table A
A
B
C

Table B
1
2
3

Result
A 1
A 2
A3
B 1
B 2
B 3
C 1
C 2
C3

Both values should be in different columns in the result table.

Anyone that knows how to do this?

Cheers

[v-danhe-msft · Accepted solution]

Hi @LeviCaers,

I have tried alena2k's function and added some steps that you could refer to:

Sample data:

Add customer column in Table A:

Expand it and format the Customer.Number as Text:

Add customer column:

Remove the other columns and you could see the correct result.

 

You could also download the pbix file the have a view:

https://www.dropbox.com/s/j8qlxt9vla8uns7/Nested%20column%20to%20new%20table.pbix?dl=0

Regards,

Daniel He

[alena2k]

You can easily do it in Power Query: add second table as a custom column for the first one and expand it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each TableB),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Custom.Column1"})
in
    #"Expanded Custom"