Forum Discussion

heetu24's avatar
heetu24
Icon for Helper I rankHelper I
2 years ago
Solved

Power Query Editor

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...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
heetu24
Icon for Helper I rankHelper I
2 years ago

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 [email protected] to discuss further on google meet.