Forum Discussion
Solved
Nested column to new table
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 b...
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
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"