Forum Discussion

Jon_R's avatar
Jon_R
Regular Visitor
3 years ago
Solved

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 m...
Need Help
  • JW_van_Holst's avatar
    JW_van_Holst
    3 years ago

    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's avatar
Jon_R
Regular Visitor
3 years ago

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's avatar
Jon_R
Regular Visitor
3 years ago

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