Forum Discussion
cool_333
3 years agoFrequent Visitor
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
jgordon11
3 years agoResolver II
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
Resultsmozgur
3 years agoHelper I
Very nice!