Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have imported and manipulated a data source upto the following stage.
SaleID Part1 Part1Desc Part2 Part2Desc Part3 Part3Desc.......
73644 rdst4 carton box yhdg3 Cushion 1 ad4fh Support sleeve
........... ..... ........ . ....... ........... so on..
I need a transformation such that it returns the following..
SaleID Part# PartDesc
73644 rdst4 carton box
73644 yhdg3 Cushion 1
73644 ad4fh Support sleeve
........ .......... ...........
Simple unpivot is not possible as the part# are intermittent inbetween their description.
So, could anyone explains the solution for this case.
Thanks in advance.
vissvess
Solved! Go to Solution.
Hi @vissvess
We can do the unpivot after some tranformations
Please see attached file for steps. I added another dummy row for testing
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjc2MzFR0lEqSikuAdHJiUUl+XkKSfkVQE5lRkq6MZB2Li3OyASKGgLZiSkmaRlAOri0oCC/qEShOCc1tSxVKVYHYpYp1CxTFLMOLQBBI6iRJkhGgsSqSpMygZRffh66obEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SaleID = _t, Part1 = _t, Part1Desc = _t, Part2 = _t, Part2Desc = _t, Part3 = _t, Part3Desc = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SaleID", Int64.Type}, {"Part1", type text}, {"Part1Desc", type text}, {"Part2", type text}, {"Part2Desc", type text}, {"Part3", type text}, {"Part3Desc", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SaleID"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each Text.Range([Attribute],4,1)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Replace([Attribute],[Custom],"")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}) in #"Removed Columns1"
Hi @vissvess
We can do the unpivot after some tranformations
Please see attached file for steps. I added another dummy row for testing
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjc2MzFR0lEqSikuAdHJiUUl+XkKSfkVQE5lRkq6MZB2Li3OyASKGgLZiSkmaRlAOri0oCC/qEShOCc1tSxVKVYHYpYp1CxTFLMOLQBBI6iRJkhGgsSqSpMygZRffh66obEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SaleID = _t, Part1 = _t, Part1Desc = _t, Part2 = _t, Part2Desc = _t, Part3 = _t, Part3Desc = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SaleID", Int64.Type}, {"Part1", type text}, {"Part1Desc", type text}, {"Part2", type text}, {"Part2Desc", type text}, {"Part3", type text}, {"Part3Desc", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SaleID"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each Text.Range([Attribute],4,1)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Replace([Attribute],[Custom],"")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}) in #"Removed Columns1"
Dear @Zubair_Muhammad ,
Thanks for the immediate and perfect solution. Kudos a lot for the effort taken.
Thankfully
vissvess
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
8 | |
7 | |
6 | |
6 |