transpose an 'enumeration list'

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.

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

Hi, 

Thanks for the solution PhilipTreacy  and Anonymous  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.