Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Jon_R
Regular Visitor

Converting a variable number of row items into columns

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:

Jon_R_0-1678973542659.png

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)

1 ACCEPTED SOLUTION
JW_van_Holst
Resolver IV
Resolver IV

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"

View solution in original post

4 REPLIES 4
JW_van_Holst
Resolver IV
Resolver IV

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:

 

Jon_R_0-1678984156507.png

 

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! 

 

Jon_R_1-1678984544770.png

 

Any thoughts?

 

Regards Jon

 

Jon_R
Regular Visitor

Hello JW,

 

Apologies - I had made a mistake, resulting in the last post!

 

I now get:

 

Jon_R_0-1678986785615.png

 

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
Regular Visitor

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors