Forum Discussion
Solved
Combine columns with same data in
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. ...
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"
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"
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?
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
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.
