Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kchungg
Regular Visitor

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

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.

NameColumn1Column2Column3Column4Column5Column6Column7Column8Column9Column10
Fruit Stall 1Fruit TypeApple  Orange  Watermelon  
Fruit Stall 1Weight  0.884324324  0.665225225  0.894294294
Fruit Stall 2Fruit TypeGuava        
Fruit Stall 2Weight  0.031141141      

 

The expected result is supposed to be something like this.

 

NameFruit TypeWeight
Fruit Stall 1Apple0.884324324
Fruit Stall 1Orange0.665225225
Fruit Stall 1Watermelon0.894294294
Fruit Stall 2Guava0.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.

1 ACCEPTED SOLUTION
adudani
Super User
Super User

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"

adudani_0-1701306536754.png

 

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.

 

 

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

View solution in original post

2 REPLIES 2
kchungg
Regular Visitor

@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
Super User
Super User

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"

adudani_0-1701306536754.png

 

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.

 

 

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors