Power Query Editor

[heetu24]

Hello All,

I am looking for one help for transformation in Power BI. Below is the actual format and I am looking desired output as per second screen shot.

In simple words question is Price Tier and sales values in different columns in different rows in actual data. Output looking for Price Tier and Metric (Sales Value) in one column only.

Here CY= Current Year, LY= Last Year, 2LY = Last two Year. SV = Sales Value.
Brand Name here taken randomly.

Desired OutPut
Country	ID	Company	Item	Price Tier	Period	Values	Metric
IND	1	A	X	Economy	YTD	28	SV CY
IND	1	A	X	Premium	YTD	26	SV LY
IND	1	A	X	Super Premium	YTD	15	SV 2LY

Data Source Format

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

[dufoq3 · Accepted solution]

Hi @heetu24, two different approaches here.

Result

v1

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]),
    // This removes extra spaces in column names (in your sample there were 2 spaces in 'Price  Tier LY' column name)
    ColnamesCleaned = Table.TransformColumnNames(Source, each Text.Combine(List.RemoveItems(Text.Split(_, " "), {""}), " ")),
    ChangedType = Table.TransformColumnTypes(ColnamesCleaned,{{"Product ID", Int64.Type}, {"Sales Value CY", type number}, {"Sales Value LY", type number}, {"Sales Value L2Y", type number}}),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
    ColumnsToTransform = List.Select(Table.ColumnNames(AddedIndex), (x)=> List.Contains({"Price Tier", "Sales Value"}, x, (y,z)=> Text.Contains(z,y, Comparer.OrdinalIgnoreCase))),
    ColCount = List.Count(ColumnsToTransform),
    RemovedOtherColumns = Table.SelectColumns(AddedIndex, ColumnsToTransform),
    ToTable = Table.FromList(List.Transform(Table.ToRows(RemovedOtherColumns), each Table.FromRows(List.Zip(List.Split(_, ColCount / 2) & {List.Transform(List.Select(Table.ColumnNames(RemovedOtherColumns), each Text.StartsWith(_, "Price Tier", Comparer.OrdinalIgnoreCase)), (x)=> Text.AfterDelimiter(Text.Upper(x), "TIER "))}), type table[Price Tier=text, Values=number, Metric=text])), (x)=> {x}, type table[tbl=table]),
    AddedIndex2 = Table.AddIndexColumn(ToTable, "Index", 0, 1, Int64.Type),
    StepBackAndRemovedColumns = Table.RemoveColumns(AddedIndex, ColumnsToTransform),
    MergedQueries = Table.NestedJoin(StepBackAndRemovedColumns, {"Index"}, AddedIndex2, {"Index"}, "AddedIndex2", JoinKind.LeftOuter),
    ExpandedAddedIndex2 = Table.ExpandTableColumn(MergedQueries, "AddedIndex2", {"tbl"}, {"tbl"}),
    Expandedtbl = Table.ExpandTableColumn(ExpandedAddedIndex2, "tbl", {"Metric", "Price Tier", "Values"}, {"Metric", "Price Tier", "Values"}),
    RemovedColumns = Table.RemoveColumns(Expandedtbl,{"Index"}),
    AddedPrefix = Table.TransformColumns(RemovedColumns, {{"Metric", each "SV " & _, type text}}),
    ChangedType2 = Table.TransformColumnTypes(AddedPrefix,{{"Price Tier", type text}, {"Values", type number}, {"Metric", type text}})
in
    ChangedType2

v2

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]),
    // This removes extra spaces in column names (in your sample there were 2 spaces in 'Price  Tier LY' column name)
    ColnamesCleaned = Table.TransformColumnNames(Source, each Text.Combine(List.RemoveItems(Text.Split(_, " "), {""}), " ")),
    ChangedType = Table.TransformColumnTypes(ColnamesCleaned,{{"Product ID", Int64.Type}, {"Sales Value CY", type number}, {"Sales Value LY", type number}, {"Sales Value L2Y", type number}}),
    Helper = [ ColumnsToTransform = List.Select(Table.ColumnNames(ChangedType), (x)=> List.Contains({"Price Tier", "Sales Value"}, x, (y,z)=> Text.Contains(z,y, Comparer.OrdinalIgnoreCase))),
    ColCount = List.Count(ColumnsToTransform),
    ColsZip = List.Buffer(List.Zip(List.Split(ColumnsToTransform, ColCount / 2))),
    Metric = List.Transform(List.Select(ColumnsToTransform, (x)=> Text.StartsWith(x, "Price Tier", Comparer.OrdinalIgnoreCase)), (y)=> "SV " & Text.Trim(Text.AfterDelimiter(Text.Upper(y), "TIER "))) ],
    MergedColumns = List.Accumulate(
        {0..List.Count(Helper[Metric])-1},
        ChangedType,
        (s,c)=> Table.CombineColumns(Table.TransformColumnTypes(s, {{Helper[ColsZip]{c}{1}, type text}}, "sk-SK"),{Helper[ColsZip]{c}{0}, Helper[ColsZip]{c}{1}}, Combiner.CombineTextByDelimiter("||", QuoteStyle.None), Helper[Metric]{c} ) ),
    UnpivotedColumns = Table.UnpivotOtherColumns(MergedColumns, List.RemoveItems(Table.ColumnNames(MergedColumns), Helper[Metric]) , "Metric", "Value"),
    SplitColumnByDelimiter = Table.SplitColumn(UnpivotedColumns, "Value", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Price Tier", "Values"}),
    ChangedType2 = Table.TransformColumnTypes(SplitColumnByDelimiter,{{"Values", type number}})
in
    ChangedType2

[heetu24]

Hi Thank you provide your response. Can you send transformation steps here . I am beginner in M code.

[dufoq3]

Hi, check note below my post to see how to use my query - then you will see transformation steps.

[v-xinruzhu-msft]

Hi,

Thanks for the solution @VN999  provided, and i want to offer some more information for user to refer to.

hello @heetu24 , you can create a blank query and input the following code to advanced editor in power 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Product ID", Int64.Type}, {"Company", type text}, {"Item name", type text}, {"Brand name", type text}, {"Time Group", type text}, {"Price Tier CY", type text}, {"Price Tier LY", type text}, {"Price Tier 2LY", type text}, {"Sales Value CY", Int64.Type}, {"Sales Value LY", Int64.Type}, {"Sales Value L2Y", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Brand name"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"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"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value2", List.Distinct(#"Replaced Value2"[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"

Output

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.

[heetu24]

Hello,

I review the tranformation stpes in attached PBIX file.

i tried with same data. I am getting the out put as per below screen shot.

2 Columns are generating of Price Tier and sales Value after Pivot step? can you assist in this?

[v-xinruzhu-msft]

Hi @heetu24 

Before pivoting, you need to change the L2Y and 2LY to the same value, such as you replace the L2Y to 2LY, then pivot it.

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.

[heetu24]

Here is the steps that I have done but price tier and sales value column 2 times coming after Pivot.

[heetu24]

I tried that way as you mentioned in screen shot but same out put I am getting.My email ID is heet.p.shah1@gmail.com, Is it possible to connect on google meet?

[heetu24]

Thank you  so much for your answer and attached file.

In a scenrio suppose If we can create slicer of county, Year, Time Group  and company . so in particular graph sales value of that partucular year(Current Year) and Time Group as well as Price tier of that item can reflect in a Bar chart.

To achieve this is it required to data  model? if  Yes then can  someone guide?  

Second question I have above visulization can we create from single data. I have provided sample data to understand the logic. Acutal I have 14 Lakh rows data in one single excel file.

I am happy  to know more about learning. My email ID is heet.p.shah1@gmail.com to discuss further on google meet.

[VN999]

Try with below steps:
import the table containing the initial data format.
select "Transform Data" to open Power Query Editor.

Reshape and Transform the Data:

Select Columns: In Power Query Editor,

select the columns Country, Product ID, Company, Item name, Brand name, Price Tier CY, Price Tier LY, Price Tier 2LY, Sales Value CY, Sales Value LY, Sales Value L2Y.

Unpivot Price Tiers and Sales Values:

Select columns Price Tier CY, Price Tier LY, Price Tier 2LY, Sales Value CY, Sales Value LY, Sales Value L2Y.
Go to the "Transform" tab.
Click on "Unpivot Columns". This will transform these columns into two new columns:

After unpivoting, the Attribute column will contain both the type of value ("Price Tier" or "Sales Value") and the time period ("CY", "LY", "L2Y")

Remove any unnecessary columns that were used for intermediate steps