Transforming messy table to be used in table.

[Justas4478]

Hi, I have this messy excel file supplied that has numbers in brackets and x near them.
The problem is that x acts like a mark for empty bins and numbers without x act as occupied bins.
Here is some sample pic:

Here is sample file https://we.tl/t-uuDGheLpv5 
I need to transform table so that I would be able to create this table in power bi:

Empty Storage Bins and Occupancy % can be calculated if other columns exists.

I am really unsure where to start to fix that table.
The other problem is that fix needs to be universal since file changes every week so whatever transformation is applied it should fit any new file.

[v-bofeng-msft · Accepted solution]

Hi @Justas4478 ,

@dk_dk , thanks for your concern about this case. I tried to use the sample data and implemented the result. Please check if there is anything that can be improved. Here is my solution:

1\Connect to excel and delete rows whose 'Storage Bin' is blank

2\Convert Empty Storage Bin column to a text column

3\Close and apply

4\Create 4 measures

StorgeBins in total = COUNTROWS(Sheet1) 

Empty Storage Bins = CALCULATE(COUNTROWS(Sheet1),FILTER(Sheet1,Sheet1[Empty Storage Bin]="X") )

Active Storage Bins = [StorgeBins in total]-[Empty Storage Bins]

Occupancy% = [Active Storage Bins]/[StorgeBins in total] 

5\Add a table

Best Regards,

Bof

[Justas4478]

@v-bofeng-msft Your solution is really good.
I had my solution as well it is only bunch of split by delimiter to separate numbers from combined storage type or X.
and then grouping afterwards.
This is my silly solution if anytone is interested:
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Storage Type", type text}, {"Storage Bin", type text}, {"Empty Storage Bin", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Split Column by Position" = Table.SplitColumn(#"Removed Columns", "Storage Type", Splitter.SplitTextByPositions({0, 4}, false), {"Storage Type.1", "Storage Type.2"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Position", "Storage Type.2", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Storage Type.2.1", "Storage Type.2.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Storage Type.2.2", Splitter.SplitTextByEachDelimiter({")"}, QuoteStyle.Csv, true), {"Storage Type.2.2.1", "Storage Type.2.2.2"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Empty Storage Bin", Splitter.SplitTextByEachDelimiter({"X"}, QuoteStyle.Csv, false), {"Empty Storage Bin.1", "Empty Storage Bin.2"}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Empty Storage Bin.1", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Empty Storage Bin.1.1", "Empty Storage Bin.1.2"}),
#"Split Column by Delimiter4" = Table.SplitColumn(#"Split Column by Delimiter3", "Empty Storage Bin.1.2", Splitter.SplitTextByEachDelimiter({")"}, QuoteStyle.Csv, true), {"Empty Storage Bin.1.2.1", "Empty Storage Bin.1.2.2"}),
#"Split Column by Delimiter5" = Table.SplitColumn(#"Split Column by Delimiter4", "Empty Storage Bin.2", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Empty Storage Bin.2.1", "Empty Storage Bin.2.2"}),
#"Split Column by Delimiter6" = Table.SplitColumn(#"Split Column by Delimiter5", "Empty Storage Bin.2.2", Splitter.SplitTextByEachDelimiter({")"}, QuoteStyle.Csv, true), {"Empty Storage Bin.2.2.1", "Empty Storage Bin.2.2.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"Storage Type.1", type text}, {"Storage Type.2.1", type text}, {"Storage Type.2.2.1", Int64.Type}, {"Storage Type.2.2.2", type text}, {"Empty Storage Bin.1.1", type text}, {"Empty Storage Bin.1.2.1", Int64.Type}, {"Empty Storage Bin.1.2.2", type text}, {"Empty Storage Bin.2.1", type text}, {"Empty Storage Bin.2.2.1", Int64.Type}, {"Empty Storage Bin.2.2.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Storage Type.2.1", "Storage Type.2.2.2", "Storage Bin", "Empty Storage Bin.1.1", "Empty Storage Bin.1.2.2", "Empty Storage Bin.2.1", "Empty Storage Bin.2.2.2"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Storage Type.1] <> "")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Storage Type.1", "Storage Type"}, {"Storage Type.2.2.1", "Storage Bins in total"}, {"Empty Storage Bin.1.2.1", "Active Storage Bins"}, {"Empty Storage Bin.2.2.1", "Empty Storage Bins"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Storage Type"}, {{"Storage Bins in total", each List.Sum([Storage Bins in total]), type nullable number}, {"Active Storage Bins", each List.Sum([Active Storage Bins]), type nullable number}, {"Empty Storage Bins", each List.Sum([Empty Storage Bins]), type nullable number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Grouped Rows",{{"Storage Bins in total", Int64.Type}, {"Active Storage Bins", Int64.Type}, {"Empty Storage Bins", Int64.Type}})
in
#"Changed Type2"

But I will use your solution instead the only thing that I would change is I use DIVIDE function when calculating Occupancy %.
Thank you for the help.

[dk_dk]

Hi @Justas4478 

I created a solution for you based on a simplified version of your dataset. It should work in principle the same way on your data, perhaps with the note that you would need to address some blank values you seem to have in the Empty Storage bins column.

Here is the sample I used: 

 (due to IT policy I am not able to download your attached xlsx)

1 - Power Query GroupBy and Pivot:

Referencing your data table:

let
    Source = Bins,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Empty Storage Bin] = "X" then "Empty Storage Bins" else "Active Storage Bins"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Storage Type", "Custom"}, {{"NumBins", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Custom]), "Custom", "NumBins", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Active Storage Bins", "Empty Storage Bins"})
in
    #"Replaced Value"

This will:
- add a custom column that is "Active Storage Bins" or "Empty Storage Bins" depending on whether the Empty Storage Bin column has an X or another value

- Group the rows by the Storage Type and the Custom column, with the aggregate value being a count of the rows

- Pivot the custom column so its values become columns, and the row counts become the values

- replace any nulls with 0

You can load this to your model (the table name in my example is "Bins Pivoted", and create two additional measures:

Storage Bins in total = SUM('Bins Pivoted'[Active Storage Bins])+SUM('Bins Pivoted'[Empty Storage Bins])

Occupancy % = DIVIDE(SUM('Bins Pivoted'[Active Storage Bins]),[Storage Bins in total])

Format as needed, and adding everything to a table visual will result in the following:

I hope this helps. Let me know if this is not what you were after. If you can paste your sample data directly into the forum in a tab delimited format, I can also test with that. (but as said, I cannot download your xlsx attachment unfortunately).

Best regards,

Daniel