Trying to merge alternate column data into 1 column and then create a proper dataset

[kchungg]

Hello, I have a data set that is currently tabulated like this. Currently there are only 3 columns worth of data but could extend to 6 or more.

Name	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10
Fruit Stall 1	Fruit Type	Apple			Orange			Watermelon
Fruit Stall 1	Weight			0.884324324			0.665225225			0.894294294
Fruit Stall 2	Fruit Type	Guava
Fruit Stall 2	Weight			0.031141141

The expected result is supposed to be something like this.

Name	Fruit Type	Weight
Fruit Stall 1	Apple	0.884324324
Fruit Stall 1	Orange	0.665225225
Fruit Stall 1	Watermelon	0.894294294
Fruit Stall 2	Guava	0.031141141

I have tried to unpivot, pivot and fill right using 
=Table.FromRows(List.Transform(Table.ToRows(Source), each Table.FillDown(Table.FromColumns({_}, {"Col"}), {"Col"})[Col]), Table.ColumnNames(Source)) then massaging the data from there but failed.

I also tried massaging the data using the list method but i'm abit inexperienced in that aspect.

Hoping to see if there's anybody who can help me.

[kchungg]

@adudani this is exactly what i was looking for. thank you so much!

And the code being able to tabulate the columns dynamically is the cherry on top. This helps a lot.

[adudani · Accepted solution]

Hi @kchungg ,

Create a blank query in the power query editor. Copy paste the below code into the advanced editor of the blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUXLOzynNzTOEs4zgLGM4ywTOMoWzzOAsczjLAs6yRJhsoBSrE63kVlSaWaIQXJKYk6MAsgzCD6ksALnBsaAgB0QrQLF/UWJeOrJAeGJJalFuak5+HlwQm6HhqZnpGSVI+gz0LCxMjI1ACEXUzMzUyAiEUNVamhiBEYbhRugudi9NLEtE0owdYzMGixsNjA0NTUAIKoKCYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
    // get all column names. Assuming column 1 and 2 will always have the same structure and only columns 3 onwards will change. Use the column count to create anchor columns
    ColumnNames = Table.AddColumn(Table.AddIndexColumn(Table.FromList(Table.ColumnNames(#"Promoted Headers")),"ColumnsCount",1,1),"ColumnsMerge",each if [ColumnsCount] =1 or [ColumnsCount] =2 then 0 else 1),
    // Filter out anchor columns, leaving us with a list of columns to merge
    ColumnsToMerge = Table.ToList(Table.SelectColumns(Table.SelectRows(ColumnNames, each ([ColumnsMerge] = 1)),"Column1")),
    ReferbacktoPromoteHeaders = #"Promoted Headers",
    // Merge the columns
    #"Merged Columns" = Table.CombineColumns(ReferbacktoPromoteHeaders,ColumnsToMerge,Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    // remove the delimeter ( all empty alternate columns)
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Merged", Text.Trim, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Trimmed Text", List.Distinct(#"Trimmed Text"[Column1]), "Column1", "Merged"),
    //create two lists which are combined based on index positions . One for fruit type and one for weight
    #"Fruit Type Indexed List" = Table.AddColumn(#"Pivoted Column", "Fruit Type Indexed List", each Table.ToList(Table.Transpose(Table.FromList({[Fruit Type]},Splitter.SplitTextByDelimiter("  "))))),
    #"Weight Indexed List" = Table.AddColumn(#"Fruit Type Indexed List", "Weight Indexed List", each Table.ToList(Table.Transpose(Table.FromList({[Weight]},Splitter.SplitTextByDelimiter("  "))))),
    #"Zipped List" = Table.AddColumn(#"Weight Indexed List", "Zipped List", each List.Zip({[Fruit Type Indexed List],[Weight Indexed List]})),
    // Expand the combined list giving us the values of respective fruits and weights
    #"Expand Zipped List" = Table.ExpandListColumn(#"Zipped List", "Zipped List"),
    // Reshape the listfor the output format.
    #"Reshaped List" = Table.AddColumn(#"Expand Zipped List", "Custom", each Table.Transpose(Table.FromList([Zipped List]))),
    #"Removed Other Columns" = Table.SelectColumns(#"Reshaped List",{"Name", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2"}, {"Fruit Type", "Weight"})
in
    #"Expanded Custom"

Added in some comments to explain what is happening.

Assuming the data follows this pattern when more columns are added, the code is dynamic.

Let me know if this resolves the issue.