transpose an 'enumeration list'

[BieBel]

Hello,

I have this 'enumeration list' that contains 15 different values, listed in one single column as "1;Dhr.;2;Dhr. en Mevr.;3;Juffr.;4;Mevr.;5;Mr.;6;Dr.;7;NV;8;CV;9;Comm. V.;10;BVBA;11;VOF;12;CVA;13;VZW;14;GEMEENTE;15;s.t."

I would like to split and change the orientation to get two colums giving me one colums containing a number and a second colums showing the value belonging tot the corresponding number 

 I tried several actions (transpose, split by delimiter, to rows, ...) but I don't get the desired result. How can I set this up dynamically so that, if there should be a change to the original data (e.g. "4" becomes another value of "16" would be added) I still get the correct table with 2 columns?

Thanks!

[Venkateshchiluk · Accepted solution]

Hi @BieBel  To split the enumeration list into two columns in Power Query Editor in Power BI, you can follow these steps:

1. Load your data into Power Query Editor.
2. Select the column containing the enumeration list.
3. Go to the "Transform" tab.
4. Click on "Split Column" and then select "By Delimiter."
5. In the "Split Column by Delimiter" dialog box, choose the appropriate delimiter (in this case, ";").
6. After splitting, you'll have multiple columns. Select the columns that contain the numbers and the corresponding values.
7. Go to the "Transform" tab again and select "Transpose."
8. This will change the orientation of the data, placing the numbers and values in separate rows.
9. Rename the columns as needed.
10. Finally, go to the "Home" tab and click on "Close & Apply" to load the transformed data into your Power BI report

Here's a step-by-step representation of these actions:

1)Original data:

Column
1;Dhr.;2;Dhr. en Mevr.;3;Juffr.;4;Mevr.;5;Mr.;6;Dr.;7;NV;8;CV;9;Comm. V.;10;BVBA;11;VOF;12;CVA;13;VZW;14;GEMEENTE;15;s.t.

2)After splitting and transposing:

Column1 | Column2
1 | Dhr.
2 | Dhr. en Mevr.
3 | Juffr.
4 | Mevr.
5 | Mr.
6 | Dr.
7 | NV
8 | CV
9 | Comm. V.
10 | BVBA
11 | VOF
12 | CVA
13 | VZW
14 | GEMEENTE
15 | s.t.

[PhilipTreacy · Accepted solution]

Hi @BieBel 

Download PBIX file with the example below

You can do this by 

1. Adding a column that extracts all characters from the text except numbers
2. Split the new column by delimiter ; and create new rows
3. Filter out blank rows
4. Add an Index column from 1

Regards

Phil

[v-xinruzhu-msft · Accepted solution]

Hi, 

Thanks for the solution @PhilipTreacy  and @Venkateshchiluk  offered, and i want to offer some more information for user to refer to.

hello @BieBel , you can create a blank query and put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYnLCsJAEAR/ZckHDE4ePqiTSVZB2HiREQw5JniJgq/vd9BLdVd332dKe30I+S/CeAtp/LgXHN7T5KXkP1Qk55LWuaIz1jTGhuY+zxJM0AW11VtUseMOzf13K7DLGS3ZxxRjd4poxVNekg3DFw==", 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 = let a=List.Transform(List.Numbers(1,9,1),each Number.ToText(_)),
b=List.Transform(Text.PositionOfAny(#"Changed Type"[Column1]{0},a,Occurrence.All),each if _=0 then _ else _-1)
in b,
    #"Split Column by Positions" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByPositions(Custom1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Positions", each ([Column1] <> ";")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",";","",Replacer.ReplaceText,{"Column1"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Replaced Value", "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.1", "Column1.2"})
in
    #"Split Column by Character Transition"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy · Accepted solution]

Hi @BieBel 

Download PBIX file with the example below

You can do this by 

1. Adding a column that extracts all characters from the text except numbers
2. Split the new column by delimiter ; and create new rows
3. Filter out blank rows
4. Add an Index column from 1

Regards

Phil

[Venkateshchiluk · Accepted solution]

Hi @BieBel  To split the enumeration list into two columns in Power Query Editor in Power BI, you can follow these steps:

1. Load your data into Power Query Editor.
2. Select the column containing the enumeration list.
3. Go to the "Transform" tab.
4. Click on "Split Column" and then select "By Delimiter."
5. In the "Split Column by Delimiter" dialog box, choose the appropriate delimiter (in this case, ";").
6. After splitting, you'll have multiple columns. Select the columns that contain the numbers and the corresponding values.
7. Go to the "Transform" tab again and select "Transpose."
8. This will change the orientation of the data, placing the numbers and values in separate rows.
9. Rename the columns as needed.
10. Finally, go to the "Home" tab and click on "Close & Apply" to load the transformed data into your Power BI report

Here's a step-by-step representation of these actions:

1)Original data:

Column
1;Dhr.;2;Dhr. en Mevr.;3;Juffr.;4;Mevr.;5;Mr.;6;Dr.;7;NV;8;CV;9;Comm. V.;10;BVBA;11;VOF;12;CVA;13;VZW;14;GEMEENTE;15;s.t.

2)After splitting and transposing:

Column1 | Column2
1 | Dhr.
2 | Dhr. en Mevr.
3 | Juffr.
4 | Mevr.
5 | Mr.
6 | Dr.
7 | NV
8 | CV
9 | Comm. V.
10 | BVBA
11 | VOF
12 | CVA
13 | VZW
14 | GEMEENTE
15 | s.t.