Power Query Help

Hi,

foodd , Thanks for your concern about the problem, and i want to offer some more information for user to refer to.

hello heetu24 , you have the original case

Solved: Power Query Editor - Microsoft Fabric Community

In this case, i have offered the solution, the reason for you have multiple columns is that your column name have extra spaces, so after changing the 'L2Y' to '2LY' then you also need to add a step nefore pivoting.select the column as i offered in the following picture.

Then replace the value " " to ""

Then pivot, it can work, and you can refer to the followng query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vRzUdJRMgRiRyCOAOKUjOIMIBUZApJwTc7Py8+tBLICilJzSzNzgazg0oLUIgUQPzezFCRgZAEizEDmmCrF6kQrOXv4gUSA2AlkEhAX5xXnJQFpX8cQFLOwmW8INg7kJEMTiHGOIOOMgdgZ6sbijGIgGViaWFSSWoTNSYQFwLYYmkFcHhsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, #"Product ID" = _t, Company = _t, #"Item name" = _t, #"Brand name" = _t, #"Time Group" = _t, #"Price Tier CY" = _t, #"Price  Tier LY" = _t, #"Price Tier 2LY" = _t, #"Sales Value CY" = _t, #"Sales Value LY" = _t, #"Sales Value L2Y" = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Brand name"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Sales Value CY", Int64.Type}, {"Sales Value LY", Int64.Type}, {"Sales Value L2Y", Int64.Type}, {"Product ID", Int64.Type}, {"Company", type text}, {"Item name", type text}, {"Country", type text}, {"Time Group", type text}, {"Price Tier CY", type text}, {"Price  Tier LY", type text}, {"Price Tier 2LY", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Country", "Product ID", "Company", "Item name", "Time Group"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Tier","Tier/",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Value","Value/",Replacer.ReplaceText,{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Attribute", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Split Column by Delimiter","L2Y","2LY",Replacer.ReplaceText,{"Attribute.2"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value2"," ","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value4", List.Distinct(#"Replaced Value4"[Attribute.1]), "Attribute.1", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Metric"}}),
    #"Replaced Value3" = Table.ReplaceValue(#"Renamed Columns",each [Metric], each "SV"&[Metric],Replacer.ReplaceValue,{"Metric"})
in
    #"Replaced Value3"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.