Seperating Levels

[syasmin25]

Hello, 

I have a table that ranks pens from 1 through 5.

I am trying to seperate them out in different columns and make it look somewhat like this. Would you have any suggestion on how I can seperate them out this way?

[v-rzhou-msft]

Hi @syasmin25 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem.

Best Regards,

Rico Zhou

[Joorge_C]

Hi @syasmin25  there are probably a few ways to do this.

1st and easier I think is to Add 5 New Calculated Columns in Power Query.

E.g.   Level1 = if [Pen Level]="Level"1 then [ID] else blank

and do the same for the rest of the Calculated columns looking into the different Levels

[v-rzhou-msft · Accepted solution]

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. 

[collinq]

Hi @syasmin25 ,

I got it close.

here is my Advanced 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", "Pen Level", "Pen Level - Copy"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","Pen","Level",Replacer.ReplaceText,{"Pen Level - Copy"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[#"Pen Level - Copy"]), "Pen Level - Copy", "ID", List.Sum)
in
#"Pivoted Column"

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!