Separate entries in one cell entry to read by semi-colon

[Anonymous]

Hi all. I have multiple entries in one cell/field (up to 10) but want to read individually as seperate fields names in Power BI reports. How do you transform the data to read each entry (seperated by a semicolon) so it can calculate the total number of values in Power BI? Preferably by not creating multiple columns or rows. 

[Anonymous]

Read in your csv file to get a one-row table mwith as many columns as entries.

Then transpose the table and count the rows.

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

If you just want to calculate the total number of values, I have a simple sample that you could try.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hcu5DQAgDATBXlwB2McnUrqw6L8NxCaTTaad6lH31zFQ2LDjwInrq4JcccUVV83ufQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Custom1 = Table.AddColumn(#"Changed Type", "NewList", each Text.Split([Column1], ";")),
    Custom2 = Table.AddColumn(Custom1, "ID", each List.Count(List.FindText([NewList], "D")))
in
    Custom2

[Greg_Deckler]

@Anonymous - So 9 times out of 10, you want to split that column in Power Query and then unpivot the resulting columns so that you end up with multiple rows. It tends to make things amazingly easier. But, it is hard to say for sure without sample data and example of what you are trying to achieve.

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.