Split row into multiple rows, while combining columns

[KyleFurner]

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.

For example, here are the columns relating to critical milestones. (headers only)

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

What I need to do is split these columns out from the main dataset and separate into rows like so (mock data):

Milestone	Status	Target Date
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

I have to replicate this same process for half a dozen other report widgets, so any help with the code to achieve this would be really appreciated.

The following link was useful for the actual splitting and creation of a new table, but I don't know how to maintain the relationship between the name, status and date for individual milestones.

https://community.powerbi.com/t5/Desktop/selected-rows-and-union-of-2-columns-in-new-table/td-p/6701... 

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Text After Delimiter", each Text.AfterDelimiter([Attribute], " ", 1), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text After Delimiter","","Milestone",Replacer.ReplaceValue,{"Text After Delimiter"}),
    #"Inserted Text Range" = Table.AddColumn(#"Replaced Value", "Text Range", each Text.Middle([Attribute], 10, 1), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Range",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Text After Delimiter"]), "Text After Delimiter", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index", "Text Range"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"target Date", type datetime}})
in
    #"Changed Type"

Hope this helps.

The Original dataset is this

[KyleFurner]

Hi Ashish,

Thanks for the detailed response. I'm having some difficulty getting this to work. I already have the dataset in Power BI but these specific columns are just a handful from the whole dataset (150+ columns).

Do you have any advice for how I should tweak this?

Apologies if these are ignorant questions.

[Ashish_Mathur]

You are welcome.  Without your actual data, I really cannot help much.

[KyleFurner]

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

[v-yiruan-msft]

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

[Ashish_Mathur]

Hi,

Add the first step in the Query Editor to remove the columns which you do not ant in the output.  Let the other steps remain. 

[KyleFurner]

Thanks so much Ashish, this was very helpful!

I just have one more question. I had to replicate this process for multiple reports on risk, issues, milestones etc and it worked perfectly. But now I need to do it for a summary table which lists multiple RAG status for multiple projects, and I'm not sure how to finish.

I have gotten to the point where I have data as below:

Effectively each project has three rows where all data is the same, except row 1 has the columns populated for  "Overall status", row 2 has "Cost status" columns populated and row 3 has "Time status" columns populated.

I am trying to combine these rows or otherwise get a single row with all status data.

Any help would be greatly appreciated.

Kyle

[Ashish_Mathur]

You are welcome.  I am not clear about your question.  Share the data and show the expected result.

[KyleFurner]

Apologies Ashish, I am probably not using the right terminology.

Here is how the table looks before applying my code. I have removed some columns that get dropped in the first steps. This is due to character limits on the post.

Here is my current code:

>>>>>

let
Source = source
#"Changed Type" = Table.TransformColumnTypes(#"6657447898179460",{{"RowNumber", Int64.Type}, {"Director Approved", type logical}, {"Project", type any}, {"Reporting Cycle Date", type any}, {"OverallStatus1", type any}, {"CostStatus2", type any}, {"TimeStatus3", type any}, {"ResourceStatus4", type any}, {"ScopeStatus5", type any}, {"StakeholderStatus6", type any}, {"GovStatus7", type any}, {"BenefitsStatus8", type any}, {"QualityStatus9", type any}, {"Monthly Summary", type any}, {"Activity 1", type any}, {"Activity 2", type any}, {"Activity 3", type any}, {"Activity 4", type any}, {"Activity 5", type any}, {"Activity 6", type any}, {"Activity 7", type any}, {"Activity 8", type any}, {"Activity 9", type any}, {"Activity 10", type any}, {"Activity 11", type any}, {"Issue1", type any}, {"Issue2", type any}, {"Risk1", type any}, {"Risk2", type any}, {"Risk3", type any}, {"Risk4", type any}, {"Risk5", type any}, {"Risk6", type any}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"RowNumber", "Program", "Project", "Project phase", "Target phase completion date", "Funding source",
"Reporting Cycle Date", "OverallStatus1", "CostStatus2", "TimeStatus3"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"RowNumber", "Program", "Project", "Project phase", "Target phase completion date", "Funding source",
"Reporting Cycle Date"}, "Attribute", "Value"),
#"Insert text" = Table.AddColumn(#"Unpivoted Other Columns", "Text After Delimiter",
each if [Attribute] = "OverallStatus1" then "Overall Status"
else if [Attribute] = "CostStatus2" then "Cost Status"
else if [Attribute] = "TimeStatus3" then "Time Status"
else ""),
#"Inserted Text Range" = Table.AddColumn(#"Insert text", "Text Range", each Text.End([Attribute], 1), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text Range",{"Attribute"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
#"Removed Columns3" = Table.RemoveColumns(#"Split Column by Delimiter",{"Value.3"}),
#"Concat Status" = Table.AddColumn(#"Removed Columns3", "Concat Status", each Text.Combine({[Text After Delimiter],[Value.1], [Value.2]},":" ), type text),
#"Overall Status" = Table.AddColumn(#"Concat Status", "Overall Status", each Text.Combine({[Text After Delimiter],[Value.1], [Value.2]},":" ), type text),
#"Overall Status Clean" = Table.ReplaceValue( #"Overall Status" ,each [Overall Status],each if Text.Contains([Overall Status], "Overall") then [Overall Status] else "",Replacer.ReplaceValue,{"Overall Status"}),
#"Cost Status" = Table.AddColumn(#"Overall Status Clean", "Cost Status", each Text.Combine({[Text After Delimiter],[Value.1], [Value.2]},":" ), type text),
#"Cost Status Clean" = Table.ReplaceValue( #"Cost Status" ,each [Cost Status],each if Text.Contains([Cost Status], "Cost") then [Cost Status] else "",Replacer.ReplaceValue,{"Cost Status"}),
#"Time Status" = Table.AddColumn(#"Cost Status Clean", "Time Status", each Text.Combine({[Text After Delimiter],[Value.1], [Value.2]},":" ), type text),
#"Time Status Clean" = Table.ReplaceValue( #"Time Status" ,each [Time Status],each if Text.Contains([Time Status], "Time") then [Time Status] else "",Replacer.ReplaceValue,{"Time Status"}),
#"Removed Columns4" = Table.RemoveColumns(#"Time Status Clean",{"Value.1", "Value.2", "Text After Delimiter", "Concat Status"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns4", "Overall Status", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Overall Status.1", "Overall Status.2", "Overall Status.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Overall Status.1", type text}, {"Overall Status.2", type text}, {"Overall Status.3", type text}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Cost Status", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Cost Status.1", "Cost Status.2", "Cost Status.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Cost Status.1", type text}, {"Cost Status.2", type text}, {"Cost Status.3", type text}}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type2", "Time Status", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time Status.1", "Time Status.2", "Time Status.3"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Time Status.1", type text}, {"Time Status.2", type text}, {"Time Status.3", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Overall Status.2", "Overall Status last month"},{"Overall Status.3", "Overall Status this month"},{"Cost Status.2", "Cost Status last month"},{"Cost Status.3", "Cost Status this month"},{"Time Status.2", "Time Status last month"},{"Time Status.3", "Time Status this month"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Overall Status.1", "Cost Status.1", "Time Status.1"})
in
#"Removed Columns1"

>>>>>

And here is what the table looks like after I run the code (truncated):

What this results in is a table with three rows per project.

All three rows contain duplicate values for every column except  for the status columns ("Overall Status last month", "Overall Status this month", "Cost Status last month", "Cost Status this month", "Time Status last month", "Time Status this month").

What I need to do is combine the three rows so that each project will change from this:

To this:

Apologies, I know that this is an excessive amount of text

[v-yiruan-msft]

Hi @KyleFurner ,

You can achieve it by appling the below codes in your Advanced Editor, please find the attachment for the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7Zhba8IwFID/SsmzoL1YHX3yUnWbqVIrTEofxIWN4SZ0G/v7W6po6EkvaVGmnIdeTo/x5fuSnJwwJDppkMBfun+Pebx7Y5svrbd/f4nX7xpPz1/Xnyx5M1oGfyx23/GGJb8z9WbLbB6+++zZWbHtdvfjDGaUul7Q81da4D4FtZKOk3ejMy+YTFfaYkkpHwTHL3p0PnUxihohMSDuvhS3UQ43v+Q45ZkD5GzW8mHOOGbsQymBllS3xISWDHBRuMmI47Yg7uHlce9nctZ8zs8WLSuFyw7qU12fNtTHvbw+hwT6c1UR98eG/oxwt7nJiOPuQNzjOrhVS9DsjHpZmvEZ9aiuRxfqMRH0MI56mECPfnk9ihb0HEkKmSvLhZKoSnIHJbkXJDGPklhAkkFakrMWDqVGZ6W5SHi+PYM+egv68yD4Y6X9sU7+DNP+1Dm31MmmDKjzV2iPkj2SnumjYE+bpJpo3UpbVGG1qlzeHF2p0Z/DxUZZF0nPdUpyCl77pAueb64rSnhLuqdU4G2TVAUr8HarbC4FxQdCvwR0C0L3BOidnEk+Kr8nVD+24Lbwj6Io+gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowNumber = _t, #"Director Approved" = _t, Project = _t, Program = _t, #"Project phase" = _t, #"Target phase completion date" = _t, #"Funding source" = _t, #"Reporting Cycle Date" = _t, OverallStatus1 = _t, CostStatus2 = _t, TimeStatus3 = _t, ResourceStatus4 = _t, ScopeStatus5 = _t, StakeholderStatus6 = _t, GovStatus7 = _t, BenefitsStatus8 = _t, QualityStatus9 = _t, #"Monthly Summary" = _t, #"Activity 1" = _t, #"Activity 2" = _t, #"Activity 3" = _t, #"Activity 4" = _t, #"Activity 5" = _t, #"Activity 6" = _t, #"Activity 7" = _t, #"Activity 8" = _t, #"Activity 9" = _t, #"Activity 10" = _t, #"Activity 11" = _t, Issue1 = _t, Issue2 = _t, Risk1 = _t, Risk2 = _t, Risk3 = _t, Risk4 = _t, Risk5 = _t, Risk6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RowNumber", Int64.Type}, {"Director Approved", type logical}, {"Project", type text}, {"Program", type text}, {"Project phase", type text}, {"Target phase completion date", Int64.Type}, {"Funding source", type text}, {"Reporting Cycle Date", type text}, {"OverallStatus1", type text}, {"CostStatus2", type text}, {"TimeStatus3", type text}, {"ResourceStatus4", type text}, {"ScopeStatus5", type text}, {"StakeholderStatus6", type text}, {"GovStatus7", type text}, {"BenefitsStatus8", type text}, {"QualityStatus9", type text}, {"Monthly Summary", type text}, {"Activity 1", type text}, {"Activity 2", type text}, {"Activity 3", type text}, {"Activity 4", type text}, {"Activity 5", type text}, {"Activity 6", type text}, {"Activity 7", type text}, {"Activity 8", type text}, {"Activity 9", type text}, {"Activity 10", type text}, {"Activity 11", type text}, {"Issue1", type text}, {"Issue2", type text}, {"Risk1", type text}, {"Risk2", type text}, {"Risk3", type text}, {"Risk4", type text}, {"Risk5", type text}, {"Risk6", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"RowNumber", "Program", "Project", "Project phase", "Target phase completion date", "Funding source",
    "Reporting Cycle Date", "OverallStatus1", "CostStatus2", "TimeStatus3"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "OverallStatus1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"OverallStatus1.1", "OverallStatus1.2", "OverallStatus1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"OverallStatus1.1", type text}, {"OverallStatus1.2", type text}, {"OverallStatus1.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "CostStatus2", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"CostStatus2.1", "CostStatus2.2", "CostStatus2.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"CostStatus2.1", type text}, {"CostStatus2.2", type text}, {"CostStatus2.3", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "TimeStatus3", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"TimeStatus3.1", "TimeStatus3.2", "TimeStatus3.3"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"TimeStatus3.1", type text}, {"TimeStatus3.2", type text}, {"TimeStatus3.3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"OverallStatus1.1", "Overall Status last month"}, {"OverallStatus1.2", "Overall Status this month"},{"CostStatus2.1", "Cost Status last month"}, {"CostStatus2.2", "Cost Status this month"},{"TimeStatus3.2", "Time Status this month"}, {"TimeStatus3.1", "Time Status last month"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"OverallStatus1.3", "CostStatus2.3", "TimeStatus3.3"})
in
    #"Removed Columns"

Best Regards

[Ashish_Mathur]

Share the download link of an MS Excel workbook with 2 worksheets.  The first one should show the inout data and the other one the expected result.

[KyleFurner]

Apologies Ashish, 

Here is a link to the file.

https://drive.google.com/file/d/1wQ1CqS4V4g2QdlLta-QY1PTR40wcXYUm/view?usp=sharing

Kind regards,

Kyle

[Ashish_Mathur]

Hi,

i do not understand your question.  Someone who does will help you.  Sorry.

[KyleFurner]

No problem Ashish,

Thankyou for all of your help so far.

Kyle

[AllisonKennedy]

@KyleFurner  How are the columns named? I would suggest doing an 'unpivot columns' or 'unpivot other columns' in Power Query for this one. If they're named consistently with the milestone name, then you'll get close to what you need after a split/extract on the attribute column. 

If they are named "Milestone Name", "Milestone Status" "Milestone Target" you'll have even more success. 

If the names aren't helpful, you'll need a bit more custom function but still definitely do this in Power Query, NOT DAX. 

Please share sample column names so we can assist further if needed.

[KyleFurner]

Thanks for the quick reply Allison. Sorry if my original post wasn't clear. 

The current column names are:

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

I am looking to take a single row and split it out so that I have one column for Milestone names, one for status and one for date, but need to make sure the status and date for each milestone is in the same row as the milestone name.

I had planned to have three new columns called "Milestone Name", "Milestone Status" "Milestone Target" to achieve this purpose. 

I also wasn't sure about using the pivot/unpivot functions as these are just a handful of columns in a dataset that has over 150 columns. Would using the pivot/unpivot function affect these other columns?

Kyle