Converting a variable number of row items into columns

[Jon_R]

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)

[JW_van_Holst · Accepted 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"

[Jon_R]

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

[Jon_R]

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

[Jon_R]

Problem resolved! I simply removed unrequired data columns at the start of the query and that stops all "errors"