Many tables in one excel sheet with different column names into one dataset

[Anonymous]

Hi Experts!

I have many tables in Excel under each other and I would like to merge it to one dataset. Can´t figure out how Power Querry can  understand that new table start with each time when row include SIZE.

For example:

SIZE	7	8	9
Red	3		3
Blue			6
SIZE	4	5	6
Red			4
Black			4
SIZE	11	12	13
Green			15
Gray	15
SIZE	4	5	6
White		2
Black	3

And I would like to see it like this:

COLOR	SIZE	QTY
Red	7	3
Red	9	3
Blue	9	6
Red	6	4
Black	6	4
Green	13	15
Gray	11	15
White	5	2
Black	4	3

Thanks to all in advance!

[v-kelly-msft]

Hi  @Anonymous ,

First in the 4 original tables,do the following transformations:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvaMclXSUTIHYgsgtlSK1YlWCkpNAbKNgRhMgYScckpTIXwgMlOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"SIZE", Int64.Type}, {"Red", Int64.Type}, {"Blue", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"SIZE"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Color"}, {"Value", "Qty"}})
in
    #"Renamed Columns"

And you will see each table is transformed to below format:

Then append tables ,and you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[CNENFRNL]

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUdJRMgZiMBWrE63klFOaCuEDkZlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SIZE = _t, #"7" = _t, #"8" = _t, #"9" = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUdJRgiATpVidaCWnnMTkbGSxWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SIZE = _t, #"4" = _t, #"5" = _t, #"6" = _t]),
    Table3 =  Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTc1T0lGCIENTpVgdkGBiJYQHkYiNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SIZE = _t, #"11" = _t, #"12" = _t, #"13" = _t]),
    Table4 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/ILElV0lECIiMQFasTreSUk5icDeQYQ8SVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SIZE = _t, #"4" = _t, #"5" = _t, #"6" = _t]),

    #"Merged Tables" = Table1 & Table2 & Table3 & Table4,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Tables", {"SIZE"}, "Size", "Qty"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Qty] <> "")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"SIZE", "Color"}})
in
    #"Renamed Columns"