Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I have the following challenge. I need to convert a variable number of rows (each row has informstion related to a single project milestone) and group these into a single row containing all milestones for a single project. Each source row has a lot of columns, most of which I can ignore.
Below a description of the required result:
One row is needed per project and all the required milestone data is added to the corresponding column for that milestone. Not all milestones are defined for all projects and there might also be some invalid milestone IDs.
Can this be achieved using Power Query or DAX?
Many thanks for any ideas!
Jon
PS (I am new to PowerBI)
Solved! Go to Solution.
Here you are (in power query)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lHyDQaRAal5KZl56UCWgbE+CBkZGBkrxerAFRkBSeec/OLUFJAaQ30gQldiDCT9C1LzQApM9YEIXYEJikVGFiCL0NWYojrGRB+IEGqM4BahOxhdjSk+9xrD/Y3DvSZYAgbdvSbwcMHu3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project_id = _t, Milestone_ID = _t, Milestone_status = _t, Misstone_Date = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"project_id", "Milestone_ID"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each _,null,(x,y,z)=>y[Milestone_ID] & " " & Text.AfterDelimiter(y[Attribute], "_"),{"Attribute"})[[project_id], [Attribute], [Value]],
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
Here you are (in power query)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lHyDQaRAal5KZl56UCWgbE+CBkZGBkrxerAFRkBSeec/OLUFJAaQ30gQldiDCT9C1LzQApM9YEIXYEJikVGFiCL0NWYojrGRB+IEGqM4BahOxhdjSk+9xrD/Y3DvSZYAgbdvSbwcMHu3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project_id = _t, Milestone_ID = _t, Milestone_status = _t, Misstone_Date = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"project_id", "Milestone_ID"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each _,null,(x,y,z)=>y[Milestone_ID] & " " & Text.AfterDelimiter(y[Attribute], "_"),{"Attribute"})[[project_id], [Attribute], [Value]],
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
Hello JW,
Thanks for the quick reply 🙂
I have adapted the code as follows to use my sample Excel:
let
Source = Excel.Workbook(File.Contents("C:\Users\XXXXX\Desktop\Transpose.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Table1_Table, {"Project_ID", "Milestone_ID"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each _,null,(x,y,z)=>y[Milestone_ID] & " " & Text.AfterDelimiter(y[Attribute], "_"),{"Attribute"})[[Project_ID], [Attribute], [Value]]
in
#"Replaced Value"
This has given me the following result:
Unfortunately each milestonr record has been split into multiple records. What I need is to merge certain columns from 1-n milestones rows for the same project, into a single record per project!
Any thoughts?
Regards Jon
Hello JW,
Apologies - I had made a mistake, resulting in the last post!
I now get:
A number of columns contain "Error" witht the following message: Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[List]
Any thoughts?
Thanks Jon
Problem resolved! I simply removed unrequired data columns at the start of the query and that stops all "errors"
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |