How would you approach this scenario?

[Anonymous]

Below is my target results that I am trying to achieve (please see screenshot below). Extracting very specific call status types from a string with in a column that currently has " " as delimiters, create a list just like the list below (Machine ID)  and ultimately count the number of call status' with the option of having a filter/slicer to separate the call types.

This is my current results (see screenshot below). Notice that I was able to create the list of Call Status'. I did this by the following steps:

1. Splitting columns by delimiter 

2. Extracting text by delimiter

3. Merging all of the coulmns together

4. Splitting the columns into rows by delimiter

I know that my problem is how I created the list but my data is set up in a weird way. Imagine seeing thousands of rows that have different sets of data like this -> FS-180813012(M12700 "Warranty"), FS-180813013(M12700 "Warranty"), *FS-180619046(M12700 "Prev Maint - New Machine Sale"), FS-180209022(M12700 "Installation (New)"), 

My question to the community is how would you approach this? Each line of data has differing number and variety of call status' but all you want is to:

1. Extract every instance of call status (ex: Warranty, Change, etc...)

2. Create a list like the first screenshot (Machine ID)

3. Be have the ability to filter the individual call status' (Ex: only be able to isolate "Change") 

I am completely stumpt ='( Any guidance from this wonderful community will be such appreciated 

[Anonymous]

If you are interested in seeing what I am working with, feel free to look at my workbook

https://github.com/anthonynguyen3/Power-BI/blob/master/WireEDM%20-%20MW2%20BI2.pbix 

[AlexisOlson · Accepted solution]

The basic steps would be:

1. Split the column by "," delimiter.
2. Unpivot all the new columns.
3. Remove nulls as desired.
4. Split those new columns into their components.
5. Load this table into your report and do the concatenation aggregation on the component columns using measures.

You should be able to create filters on these component columns now.

Here's an example query based on a small subset of your data that you can paste into your query editor:

let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVFdT8MgFP0rhCdNqrlA14/HZUbdQ93M+rbuobZkJSIkDGr89942btGlNYRLyLkczj1nv6ebpx1ZmzacvFPyRCO6fHfW4Ll5xvK4u2MZLDgH4DdFLjgnFV11tTvKit5G5AcHBlxc8K2TvTS+9qqXpKiV8b96Y6Ri+TwX5wxAzOEpZLjjeB5PB47Z9wnjgDibwOkhQjec65W2Bi1p7gcTxruWf/0QPAGBfyxYMjnDFf6PHyn2MfRkgmvU8xpqrfwXKa3VpAqYQkIe1CCnkHV7lrZdYhn7SxuarrWfhpSy6YzV9oipnqdZWR0+3sKQ8vrlMk2CC9DROM/iKwWHbw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [site_name = _t, city = _t, state_id = _t, FS_ticket_within_2yr = _t]),
 #"Split Column by Delimiter" = Table.SplitColumn(Source, "FS_ticket_within_2yr", Splitter.SplitTextByDelimiter(", ", QuoteStyle.None), {"FS_ticket_within_2yr.1", "FS_ticket_within_2yr.2", "FS_ticket_within_2yr.3", "FS_ticket_within_2yr.4", "FS_ticket_within_2yr.5", "FS_ticket_within_2yr.6", "FS_ticket_within_2yr.7", "FS_ticket_within_2yr.8"}),
 #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"site_name", "city", "state_id"}, "Attribute", "Value"),
 #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each [Value] <> null),
 #"Split Column by Delimiter1" = Table.SplitColumn(#"Filtered Rows", "Value", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.None, false), {"Value.1", "Value.2"}),
 #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Value.2", Splitter.SplitTextByDelimiter("""", QuoteStyle.None), {"Value.2.1", "Value.2.2", "Value.2.3"}),
 #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Value.2.3", "Attribute"}),
 #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value.1", "FS"}, {"Value.2.1", "M"}, {"Value.2.2", "Descr"}})
in
 #"Renamed Columns"

[Anonymous]

STUNNING! So much gratitude...thank you!

How on earth did you come up with that? There is 100% chance that I would have never have got that! 

[AlexisOlson]

Lots of practice. I haven't been on these forums much yet, but I've been self-learning Power BI by answering questions on StackOverflow for over a year by now.

[Anonymous]

Stack Overflow? I will start checking it out now because I am learning self-teaching myself DAX, PowerBI, SQL, and Python! Thank you for the advice! 

[Anonymous]

This is what my data looks like in Power Query (please see screenshot below). If we take a look at the first 4 lines: 

Line #1 = Null --> Output Column has commas bc I merged together with delimiters

Line #2 = Null --> Output Column has commas bc I merged together with delimiters

Line #3 = has 2 Call Status' (Charge + Warranty) --> Output Column has Charge + Warranty but has the multiple commas from the merged columns

Line #4 = has 2 Call Status' (Prev maint - New Machine Sale + Installation (New)) --> Output Column has the 2 call status' but has the multiple commas from the merged columns

My goal is to have only the call status' without any commas (either trailing or beginning the call status') and the ability to use a slicer/filter to isolate the call status by counting the total number of individual call status' (ex: use filter to isolate "Charge")