Power Query Help

[heetu24]

Hello All I have a data in below format after Pivot Table Step. And M Code is = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "Year"}, {"Attribute.1", "Attribute"}}).


New Format I am looking that Price Tier and sales Value should be in one column.

Can any one suggest M code/Any further steps.

Country	Product ID	Company	Item name	Brand name	Time Group	Attribute.2	Price Tier	Price  Tier	Price Tier	Sales Value	Sales Value
CAN	3	C	Z	shs	Quarter	CY	Super Premmium	null	null	18	null
CAN	3	C	Z	shs	Quarter	LY	null	Super Premmium	null	16	null
CAN	3	C	Z	shs	Quarter	2LY	null	null	Super Premmium	null	26
CHN	2	B	Y	snsnb	MAT	CY	Premuim	null	null	18	null
CHN	2	B	Y	snsnb	MAT	LY	null	Economy	null	21	null
CHN	2	B	Y	snsnb	MAT	2LY	null	null	Premuim	null	14
IND	1	A	X	dhsh	YTD	CY	Economy	null	null	28	null
IND	1	A	X	dhsh	YTD	LY	null	Premuim	null	26	null
IND	1	A	X	dhsh	YTD	2LY	null	null	Super Premmium	null	15

[v-xinruzhu-msft · Accepted solution]

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.

[foodd]

Hello @heetu24 , and thank you for sharing a question with the Community.  This reply is informational. Please follow the decorum of the Community Forum when asking a question.

Please share your work-in-progress Power BI Desktop file (with sensitive information removed) and any source files in Excel format that fully address your issue or question in a usable format (not as a screenshot). You can upload these files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then provide the file's URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!