Seperating Levels

Hi syasmin25 

In addition to collinq reply, if you want to achieve your goal by Power Query Editor, collinq 's reply is close.

I add two steps duplicate ID column and Fill up the level1-level5 column.

You can copy my M query into your advance editor.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRCkjNUzBUitUB8Y2hfCMo3xTKN4byLaB8EyjfEso3VYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Pen Level" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Pen Level", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "ID", "ID - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Pen Level", "Pen Level - Copy"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column1",{"ID", "ID - Copy", "Pen Level", "Pen Level - Copy"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns","Pen","Level",Replacer.ReplaceText,{"Pen Level - Copy"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[#"Pen Level - Copy"]), "Pen Level - Copy", "ID - Copy"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"Level 1", "Level 2", "Level 3", "Level 4", "Level 5"})
in
    #"Filled Up"

Or you can achieve your goal by building a calculate table.

NewC_Table = 
ADDCOLUMNS (
    C_Table,
    "Level1",
        IF (
            C_Table[Pen Level] <= "Pen 1",
            MAXX ( FILTER ( C_Table, C_Table[Pen Level] <= "Pen 1" ), C_Table[ID] ),
            BLANK ()
        ),
    "Level2",
        IF (
            C_Table[Pen Level] <= "Pen 2",
            MAXX ( FILTER ( C_Table, C_Table[Pen Level] <= "Pen 2" ), C_Table[ID] ),
            BLANK ()
        ),
    "Level3",
        IF (
            C_Table[Pen Level] <= "Pen 3",
            MAXX ( FILTER ( C_Table, C_Table[Pen Level] <= "Pen 3" ), C_Table[ID] ),
            BLANK ()
        ),
    "Level4",
        IF (
            C_Table[Pen Level] <= "Pen 4",
            MAXX ( FILTER ( C_Table, C_Table[Pen Level] <= "Pen 4" ), C_Table[ID] ),
            BLANK ()
        ),
    "Level5",
        IF (
            C_Table[Pen Level] <= "Pen 5",
            MAXX ( FILTER ( C_Table, C_Table[Pen Level] <= "Pen 5" ), C_Table[ID] ),
            BLANK ()
        )
)

Result is as below.

You can download the pbix file from this link: Seperating Levels

Best Regards,

Rico Zhou

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