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 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)

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"

4 Replies

  • JW_van_Holst's avatar
    JW_van_Holst
    Icon for Resolver IV rankResolver 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