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"
JW_van_Holst's avatar
JW_van_Holst
Resolver IV
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,

     

    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'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"