Forum Discussion

KyleFurner's avatar
KyleFurner
Frequent Visitor
5 years ago

Split row into multiple rows, while combining columns

Hi all,   I have inherited a dataset which I need to manipulate in Power BI to track project progress. All information is captured monthly in a single table, so all 150+ columns in a single row.  ...
Need Help
KyleFurner's avatar
KyleFurner
Frequent Visitor
5 years ago

I understand. I have created a sample dataset which will demonstrate what I mean.

 

Essentially I have a dataset where each project's monthly reporting data is contained. 

I need to create a subset of this data only containing the information from the following columns:

  • Milestone 1
  • Milestone 1 Status
  • Milestone 1 target Date
  • Milestone 2
  • Milestone 2 Status
  • Milestone 2 target Date
  • Milestone 3
  • Milestone 3 Status
  • Milestone 3 target Date

However, I need to display the data like this:

So I am looking for help to split an this data so that a single row, which has data on multiple milestones for a single project, can be a subset containing a row for each milestone, it's date and it's status. But I don't want to include the extra columns, which in the example above contain data on project RAG ratings, (time/cost/overall status).

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi KyleFurner ，

You can achieve it by using Merge columns and Unpivot function, the full applied codes as follow. You can also find the attachment for the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZMxD8IgEIX/C3OTluNI3R2cutix6SabVWP8/5GUtlJ8L25OEL5w3+MuDIPpemsqc7xPj2t4hUvcn54h3OLa9UKJo0Qp8ZCM1ZrB1baWRuxXhAgAcTMRQJQV8wm0e5InEJpgI+c5/OYHN5SVWvwH7nfUD0g5hDWZslK/J6DMD4BjQBlI75cmb2Vu98wOQLJbQJRd8Qh8/KiV5Q/Ih89aj87Lzu9f/nfz+AY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Milestone 1" = _t, #"Milestone Target Date 1" = _t, #"Milestone Status 1" = _t, #"Milestone 2" = _t, #"Milestone Target Date 2" = _t, #"Milestone Status 2" = _t, #"Milestone 3" = _t, #"Milestone Target Date 3" = _t, #"Milestone Status 3" = _t, #"Milestone 4" = _t, #"Milestone Target Date 4" = _t, #"Milestone Status 4" = _t, #"Milestone 5" = _t, #"Milestone Target Date 5" = _t, #"Milestone Status 5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Milestone 1", type text}, {"Milestone Target Date 1", type text}, {"Milestone Status 1", type text}, {"Milestone 2", type text}, {"Milestone Target Date 2", type text}, {"Milestone Status 2", type text}, {"Milestone 3", type text}, {"Milestone Target Date 3", type text}, {"Milestone Status 3", type text}, {"Milestone 4", type text}, {"Milestone Target Date 4", type text}, {"Milestone Status 4", type text}, {"Milestone 5", type text}, {"Milestone Target Date 5", type text}, {"Milestone Status 5", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Milestone 1", "Milestone Target Date 1", "Milestone Status 1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Milestone 2", "Milestone Target Date 2", "Milestone Status 2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Milestone 3", "Milestone Target Date 3", "Milestone Status 3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.2"),
    #"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"Milestone 4", "Milestone Target Date 4", "Milestone Status 4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.3"),
    #"Merged Columns4" = Table.CombineColumns(#"Merged Columns3",{"Milestone 5", "Milestone Target Date 5", "Milestone Status 5"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.4"),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Merged Columns4", {"Merged", "Merged.1", "Merged.2", "Merged.3", "Merged.4"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Only Selected Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value.1", "Milestone"}, {"Value.2", "Target Date"}, {"Value.3", "Status"}})
in
    #"Renamed Columns"

Best Regards