Help with being able to track multiple single items in a column for different tracking seperated.

[Marcus-G]

Hey! I have a question. So the data set that im working with has a lot of data but I want to track per item that is assigned to a single individual/line as the company wants to know the trends of each individual item for different companies over various timelines. In their dataset they track all the tests/items for an individual in a single column which leads to hundreds of variations as each indivdual could have anywhere from one to many different items which are split with a line delimiter. I will put an example piece using random info as an example down below.

 

I am hoping there is a way that I can either break up the information or create a way that I can track each individual item on a bar graph to show the volumns either per company or over a certain timeframe.

Thank you for your time!!!

[Peter_Beck · Accepted solution]

Hi -

If I understand you correctly, you want to start with something like this:

... and transform it into this:

This is pretty easily done with the "Split Column By Delimeter" option in the "Transform" tab. Select "Split Column" and then "By Delimeter", and then select the delimeter in question (in this case, "|"), and chose "New Rows" as the option.

Afterwards, you may need to choose "Format" and then "Trim" to get rid of extra spaces.

Here is a script showing the steps:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRcnRyVnDOzy1IzKsE8pwz8pPzcxJLUhVqFMIS8zJzchKBrOCSosTypNSiokqlWB2QRiOgUkMjY/wakdSGZCSWICkmwjzslscCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderNo = _t, Company = _t, Items = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderNo", Int64.Type}, {"Company", type text}, {"Items", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Items", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Items"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Items", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Items", Text.Trim, type text}})
in
#"Trimmed Text"

Hope this helps!

Peter.