Power Query - Return column name each time specific cell value is detected?

Question

Power Query - Return column name each time specific cell value is detected?

M, Johannes 86

Hey guys!
I got this Table in the Power Query Editor. There are several Persons with different Attributes.

How can I get my table in a format like this in the Power Query Editor? The first row schould be the person and the second is filled with the attributes were the value is one. They should be in a single row with a seperating figure.

If you could help me that would be awesome and would safe my day!

Emi Zhang-MSFT 30,126 Reputation points Microsoft External Staff

2020-10-30T09:07:21.8+00:00

Hi @
Just checking in to see if the information of Lz-3068 was helpful. Please let us know if you would like further assistance.

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer accepted by question author

1 additional answer

Your answer

Emi Zhang-MSFT 30,126 Reputation points Microsoft External Staff

2020-10-30T09:07:21.8+00:00

Hi @
Just checking in to see if the information of Lz-3068 was helpful. Please let us know if you would like further assistance.

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 1

Lz._ 38,106 Volunteer Moderator

Hi

    PreviousStepName = ...
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(PreviousStepName, {"Person"}, "Attribute", "Value"),
    FilteredValues = Table.SelectRows(UnpivotedOtherColumns, each ([Value] = 1)),
    GroupedRows = Table.Group(FilteredValues, {"Person"},
        {{"Attributes", each Text.Combine([Attribute],", "), type text}}
    )

M, Johannes 86 Reputation points

2020-10-30T13:11:29.853+00:00

Thank you very much again! This helped a lot.

Answer 2

Ashish Mathur 100.8K Volunteer Moderator

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Attributes", each Text.Combine(Table.SelectRows(Record.ToTable(_), each [Value]=1)[Name],", "))[[Person],[Attributes]]
in
    #"Added Custom"

Hope this helps.

User's image

Share via

Power Query - Return column name each time specific cell value is detected?

1 additional answer

Your answer