Combine rows and replace null values

[KyleFurner]

Hi all,

I have a table which holds monthly reporting data for a number of different projects. Each project takes up a single row. 

Within the table there are a number of columns for different status types (overall, cost, time etc). These columns contain concatenated data of "last month status";"this month status";"commentary".

What I am trying to do is separate these columns so that I can drop the commentary but have unique columns showing the following:

• Overall Status last month
• Overall Status this month
• Cost Status last month
• Cost Status this month
• Time Status last month
• Time Status this month

Here is how the table looks after dropping columns that I do not need, but before applying the rest of the steps:

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:

This results in a table with three rows per project; one for the 'Overall status' columns, one for 'Cost status', and one for 'Time status'. All values in columns "RowNumber" to "Text Range" are duplicated.

What I need to do is combine the three rows so that a single row contains all relevant status info, rather than null values.

The data for each project will change from this:

To this:

If anyone can help me achieve this I would greatly appreciate it.

Kyle

[Vera_33 · Accepted solution]

Hi @KyleFurner 

If I understand it correctly, you want to extract the first two values for the 3 columns then split to two as last month and this month, so you can add a custom column to do it, I did only for OverallStatus, you can paste it in Advanced Editor to see it

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoJCnUFUgFF+VmpySWOEGZ6UWIuSDIgI7E4FcQwMjACUcH5pUXJqQogVcaG+gbG+lDxoNQU68jUnJz8cmsCPGtCRKxOtJIRhrucsLjLiDh3gbCzv6+vq1+IY1CkQohrRAheGahboRSmPBYEcrMxhpudyQ9Layw0NntjAQ==", 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]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"RowNumber", "Program", "Project", "Project phase", "Target phase completion date", "Funding source","Reporting Cycle Date","OverallStatus1", "CostStatus2", "TimeStatus3"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Overall Status last month", each List.FirstN( Text.Split([OverallStatus1],";"),2){0}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Overall Status this month", each List.FirstN( Text.Split([OverallStatus1],";"),2){1})
in
    #"Added Custom1"