cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Seperating Levels

Hello, 

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

syasmin25_0-1603200298539.png


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?

syasmin25_1-1603200428416.png

 




 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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.

1.png

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. 

View solution in original post

4 REPLIES 4
Highlighted
Memorable Member
Memorable Member

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!
Highlighted
Community Support
Community Support

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.

1.png

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. 

View solution in original post

Highlighted
Resolver II
Resolver II

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

Highlighted
Community Support
Community Support

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors