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 OutPutCountryIDCompanyItemPrice TierPeriodValuesMetricIND1AXEconomyYTD28SV CYIND1AXPremiumYTD26SV LYIND1AXSuper PremiumYTD15SV 2LYData Source FormatCountryProduct IDCompanyItem nameBrand nameTime GroupPrice Tier CYPrice Tier LYPrice Tier 2LYSales Value CYSales Value LYSales Value L2YIND1AXdhshYTDEconomyPremuimSuper Premmium282615CHN2BYsnsnbMATPremuimEconomyPremuim182114CAN3CXshsQuarterSuper PremmiumSuper PremmiumSuper Premmium181626

Forum Discussion

Helper 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 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

Need Help

dufoq3

2 years ago

Hi heetu24, two different approaches here.

Result

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

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

10 Replies

VN999
Resolver I
2 years ago
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

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.

Power Query Editor.pbix31 KB

heetu24
Helper 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.
heetu24
Helper I
2 years ago
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?
- Anonymous
  Not applicable
  2 years ago
  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.

dufoq3

Community Champion

2 years ago

Hi heetu24, two different approaches here.

Result

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

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
Helper I
2 years ago
Hi Thank you provide your response. Can you send transformation steps here . I am beginner in M code.
- dufoq3
  Community Champion
  2 years ago
  Hi, check note below my post to see how to use my query - then you will see transformation steps.

Forum Discussion

Power Query Editor

10 Replies

Related content

power query editor slow loading

SLOW Power Query Editor

Power Query Editor cannot be used

Changing date format in Power Query Editor

Vlookup in Power Query Editor