Combine columns with same data in

[mwinds]

Hi all,

I have a Excel file which has a list of properties and several installations installed on each one which are separated by each column. E.g. Measure 1:, Measure 2, Measure 3 ect.

I'd like to make a chart to show the total installations installed instead of having different charts for each measure. How would i do this in power bi? The way the excel spreadsheet has been set up, it will be quite difficult to get all of the measures installed under one column.

Any help is appreciated.

Thank you

[mwinds]

Anyone able to look into this? A lot of the rows are getting mixed up it seems. 

Every row should have a RSL and a property ID assigned to them but there's 200 rows without. This is worrying me that the rest of the data is also mixed up. On my excel spreadsheet all of the rows do have data in. 

let
Source = Excel.Workbook(File.Contents("filepath"), null, true),
#"Property Reporting_Sheet" = Source{[Item="Property Reporting",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Property Reporting_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Property ID", type any}, {"Property Type", type any}, {"Pre EPC", type any}, {"Cost of all measures", Int64.Type}, {"Government Funding", type number}, {"LA Funding (Non-SHDF)", type number}, {"Self-funding", type number}, {"No. of measures to install", Int64.Type}, {"Property Installations Status", type text}, {"Post EPC", type text}, {"RSL", type text}, {"LEP", type any}, {"LA", type any}, {"Measure 1", type any}, {"Measure 1 cost", type number}, {"Measure 1 Status", type text}, {"Measure 2", type any}, {"Measure 2 cost", type number}, {"Measure 2 Status", type text}, {"Measure 3", type any}, {"Measure 3 cost", type number}, {"Measure 3 Status", type text}, {"Measure 4", type any}, {"Measure 4 cost", type number}, {"Measure 4 Status", type text}, {"Measure 5", type any}, {"Measure 5 cost", Int64.Type}, {"Measure 5 Status", type text}, {"Measure 6", type any}, {"Measure 6 cost", Int64.Type}, {"Measure 6 Status", type text}, {"Completed Measures", Int64.Type}, {"Measures not started", Int64.Type}, {"Measures Started", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Type", each Text.AfterDelimiter([Attribute], " ", 1), type text),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Type 2", each if [Attribute] = "RSL" then "RSL" else if [Attribute] = "LEP" then "LEP" else if [Attribute] = "LA" then "LA" else if [Attribute] = "Measure 1" then "Measure" else if [Attribute] = "Measure 1 cost" then "Cost" else if [Attribute] = "Measure 1 Status" then "Status" else if [Attribute] = "Measure 2" then "Measure" else if [Attribute] = "Measure 2 cost" then "Cost" else if [Attribute] = "Measure 2 Status" then "Status" else if [Attribute] = "Measure 3" then "Measure" else if [Attribute] = "Measure 3 cost" then "Cost" else if [Attribute] = "Measure 3 Status" then "Status" else if [Attribute] = "Measure 4" then "Measure" else if [Attribute] = "Measure 4 cost" then "Cost" else if [Attribute] = "Measure 4 Status" then "Status" else if [Attribute] = "Measure 5" then "Measure" else if [Attribute] = "Measure 5 cost" then "Cost" else if [Attribute] = "Measure 5 Status" then "Status" else if [Attribute] = "Measure 6" then "Measure" else if [Attribute] = "Measure 6 cost" then "Cost" else if [Attribute] = "Measure 6 Status" then "Status" else if [Attribute] = "Property ID" then "Property ID" else ""),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each [Type 2] <> null and [Type 2] <> ""),
#"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Type 2"}, {{"Count", each _, type table [Index=number, Attribute=text, Value=any, Type=text, Type 2=text]}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ([Count], "IndexValue",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"IndexValue", "Value"}, {"IndexValue", "Value"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Type 2"]), "Type 2", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Property ID", type text}})
in
#"Changed Type1"

[MFelix]

Hi @mwinds,

Not forgotten about you but yesterday and today was busy at work. Will get to this tomorrow morning. 

Sorry for the delay

[MFelix]

Hi @mwinds ,

In this case you need to unpivot your column and values into a single table, you can do this using the Power Query within Power BI, try the steps below:

• Add an index column
• Unpivot other columns by the index column
• Add a column with the names of the columns you need in this case I used the Text after delimiter option (called it type).
• Replaced the blank values on the new column by the text Measure
• Add a grouped by the Type column
• Added a custom column with the following code:

Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ([Count], "IndexValue",1))

• Expanded the columns Valu and Index
• Remove the column count (the one with the table on it's values
• Pivot columns by the type select the values and don't aggregate.

Total code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7PSSxSCAhT0lEyNTAwAFKeecUliTmJJSWZ+XkKzvm5BTmpJalAcedwTyBpjF+RS35pUk6qgntOYlVmXjpQwNwMj/pYnVEHjDpgoB0QCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Measure 1" = _t, #"Measure 1 Cost" = _t, #"Measure 1 Status" = _t, #"Measure 2" = _t, #"Measure 2 Cost" = _t, #"Measure 2 Status" = _t, #"Measure 3" = _t, #"Measure 3 Cost" = _t, #"Measure 3 Status" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Type", each Text.AfterDelimiter([Attribute], " ", 1), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text After Delimiter","","Measure",Replacer.ReplaceValue,{"Type"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Type"}, {{"Count", each _, type table [Index=number, Attribute=text, Value=text, Text After Delimiter=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ([Count], "IndexValue",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value", "IndexValue"}, {"Value", "IndexValue"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Value")
in
    #"Pivoted Column"

[mwinds]

Hey, thanks for the really detailed reply. I'm not super at DAX and quite new to power bi.

The file I'm using has a lot more columns in it so when using the text.after delimiter, it leaves a lot of the columns blank. so when replacing the blanks with measure, it still includes a lot of columns that aren't needed. I've just used a lot of if statements for now for all of the columns i need. 

When i group by type and add the custom column i get this:

I'm unable to do the next step it seems. Guess I've gone wrong somewhere. Any idea?

Do you have a file i could look at?

[MFelix]

Hi @mwinds ,

This is not done in DAX this is done in the Power Query so using M, the other columns are for different informations?

See file with example attached

[mwinds]

Hi,

Thanks for this. I have followed all the steps and i thought it had worked but some cells seem to have gone a bit AWOL. For example on my excel spreadsheet where my data is from, each measure installed is assigned a "RSL" (and installer). However, on power BI there are installations without a RSL next to it.

There is around 200 rows like this. Any idea why this would be? I can try to send over a power bi file with the sensitive info redacted next week if that helps.

[MFelix · Accepted solution]

Hi @mwinds ,

SInce you have more columns I did the following:

• Deleted all the columns except the measures and the property ID
• Did all the steps until the expanded like in the previous option
• Add a column wiht the merge of property ID and the index value
• Removed the Count property ID and Index value
• Did the Pivot by the new column of the merge
• Extracted the property ID na coluna merge
• Made a merge with the stpe Changed Type (4th step on the query) by property ID
• Expande all other columns we need:

let
    Source = Excel.Workbook(File.Contents("C:\Teste\Testdata.xlsx"), null, true),
    #"Property Reporting_Sheet" = Source{[Item="Property Reporting",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Property Reporting_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Property ID", type any}, {"Property Type", type any}, {"Pre EPC", type any}, {"Cost of all measures", Int64.Type}, {"Government Funding", type number}, {"LA Funding (Non-SHDF)", type number}, {"Self-funding", type number}, {"No. of measures to install", Int64.Type}, {"Property Installations Status", type text}, {"Post EPC", type text}, {"RSL", type text}, {"LEP", type any}, {"LA", type any}, {"Measure 1", type any}, {"Measure 1 cost", type number}, {"Measure 1 Status", type text}, {"Measure 2", type any}, {"Measure 2 cost", type number}, {"Measure 2 Status", type text}, {"Measure 3", type any}, {"Measure 3 cost", type number}, {"Measure 3 Status", type text}, {"Measure 4", type any}, {"Measure 4 cost", type number}, {"Measure 4 Status", type text}, {"Measure 5", type any}, {"Measure 5 cost", Int64.Type}, {"Measure 5 Status", type text}, {"Measure 6", type any}, {"Measure 6 cost", Int64.Type}, {"Measure 6 Status", type text}, {"Completed Measures", Int64.Type}, {"Measures not started", Int64.Type}, {"Measures Started", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Property Type", "Pre EPC", "Cost of all measures", "Government Funding", "LA Funding (Non-SHDF)", "Self-funding", "No. of measures to install", "Property Installations Status", "Post EPC", "RSL", "LEP", "LA", "Completed Measures", "Measures not started", "Measures Started", "Not reported"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Property ID"}, "Attribute", "Value"),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Unpivoted Columns", "Type", each Text.AfterDelimiter([Attribute], " ", 1), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text After Delimiter","","Measure",Replacer.ReplaceValue,{"Type"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Type"}, {{"Count", each _, type table [Property ID=number, Attribute=text, Value=any, Text After Delimiter=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ([Count], "IndexValue",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Property ID", "Value", "IndexValue"}, {"Property ID", "Value", "IndexValue"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Custom", "Merged", each Text.Combine({Text.From([Property ID], "en-150"), Text.From([IndexValue], "en-150")}, "|"), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Property ID", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Count", "Property ID", "IndexValue"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Type"]), "Type", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Pivoted Column", {{"Merged", each Text.BeforeDelimiter(_, "|"), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Merged", "Property ID"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Property ID", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Property ID", Order.Ascending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Property ID"}, #"Changed Type", {"Property ID"}, "Sorted Rows", JoinKind.LeftOuter),
    #"Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries", "Sorted Rows", {"Property Type", "Pre EPC", "Cost of all measures", "Government Funding", "LA Funding (Non-SHDF)", "Self-funding", "No. of measures to install", "Property Installations Status", "Post EPC", "RSL", "LEP", "LA", "Completed Measures", "Measures not started", "Measures Started", "Not reported"}, {"Property Type", "Pre EPC", "Cost of all measures", "Government Funding", "LA Funding (Non-SHDF)", "Self-funding", "No. of measures to install", "Property Installations Status", "Post EPC", "RSL", "LEP", "LA", "Completed Measures", "Measures not started", "Measures Started", "Not reported"})
in
    #"Expanded Sorted Rows"

[mwinds]

Really appreciate your help, thank you!

[MFelix]

Hi @mwinds 

That will be helpfull.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[mwinds]

Thank you. I have sent over the files via PM.