Forum Discussion
Help with power query
Hi, guys need help with power query transformation below is the raw data that need the output as 2nd table. Thanks in advance. Raw data
I am very interested in seeing another way of doing this since I am not very good at using Pivot columns.
Until someone else comes up with a better approach, here is my way:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4hKTVTSUXLMSykC0eb65vpGBkamQKalvpEhiG0JZBuZGgBJQyMQYW4AYhsDRWJ1opW8EgsS84B8p8SsxCzcBhiCDTA2QRhgaGSsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), {"ProjectRef", "ModelDataSet", "ProjectStart", "ProjectEnd", "FacilitiesUnitRate", "FacilitiesQuantity", "ServiceUnitRate", "ServiceQuantity"}),
// Sample binary data used for testing. Use the following line instead to get data from the table in the real implementation.
// Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangeTypes = Table.TransformColumnTypes(Source,{{"ProjectRef", type text}, {"ModelDataSet", type text}, {"ProjectStart", type date}, {"ProjectEnd", type date}, {"FacilitiesUnitRate", Int64.Type}, {"FacilitiesQuantity", Int64.Type}, {"ServiceUnitRate", Int64.Type}, {"ServiceQuantity", Int64.Type}}),
UnpivotColumns = Table.Unpivot(ChangeTypes, {"FacilitiesUnitRate", "FacilitiesQuantity", "ServiceUnitRate", "ServiceQuantity"}, "Category", "Value"),
Quantity = Table.AddIndexColumn(Table.RenameColumns(Table.ReplaceValue(Table.SelectRows(UnpivotColumns, each Text.EndsWith([Category], "Quantity")),"Quantity","",Replacer.ReplaceText,{"Category"}), {{"Value", "Quantity"}}), "Index"),
UnitRate = Table.AddIndexColumn(Table.RenameColumns(Table.ReplaceValue(Table.SelectRows(UnpivotColumns, each Text.EndsWith([Category], "UnitRate")),"UnitRate","",Replacer.ReplaceText,{"Category"}), {{"Value", "Unit Rate"}}), "Index"),
JoinOnIndex = Table.NestedJoin(UnitRate, "Index", Quantity, "Index", "Join"),
ExpandQuantity = Table.ExpandTableColumn(JoinOnIndex, "Join", {"Quantity"}),
RemoveIndex = Table.RemoveColumns(ExpandQuantity,{"Index"})
in
RemoveIndex
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4hKTVTSUXLMSykC0eb65vpGBkamQKalvpEhiG0JZBuZGgBJQyMQYW4AYhsDRWJ1opW8EgsS84B8p8SsxCzcBhiCDTA2QRhgaGSsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), {"ProjectRef", "ModelDataSet", "ProjectStart", "ProjectEnd", "FacilitiesUnitRate", "FacilitiesQuantity", "ServiceUnitRate", "ServiceQuantity"}), ChangeTypes = Table.TransformColumnTypes(Source,{{"ProjectRef", type text}, {"ModelDataSet", type text}, {"ProjectStart", type date}, {"ProjectEnd", type date}, {"FacilitiesUnitRate", Int64.Type}, {"FacilitiesQuantity", Int64.Type}, {"ServiceUnitRate", Int64.Type}, {"ServiceQuantity", Int64.Type}}), AddCategoryColumn = Table.AddColumn(ChangeTypes, "Category", each {"Facilities", "Service"}), ExpandCategory = Table.ExpandListColumn(AddCategoryColumn, "Category"), AddRecordColumn = Table.AddColumn(ExpandCategory, "Custom", each if [Category] = "Facilities" then [Rate = [FacilitiesUnitRate], Quantity = [FacilitiesQuantity]] else [Rate = [ServiceUnitRate], Quantity = [ServiceQuantity]]), RemoveColumns = Table.RemoveColumns(AddRecordColumn,{"FacilitiesUnitRate", "FacilitiesQuantity", "ServiceUnitRate", "ServiceQuantity"}), Result = Table.ExpandRecordColumn(RemoveColumns, "Custom", {"Rate", "Quantity"}) in ResultVery nice!
