Create a new table based on specific row column data

Hi Anonymous ,

I create a table as you mentioned.

Then I think you can go to Power Query and you can use Advanced Editor.

let
    Source = Table.FromRecords({
        [Number="A", A=null, B=1, C=1, D=null],
        [Number="B", A=1, B=null, C=1, D=null],
        [Number="C", A=1, B=1, C=null, D=null],
        [Number="D", A=null, B=null, C=null, D=null]
    }),
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Number"}, "Attribute", "Value"),
    Filtered = Table.SelectRows(Unpivoted, each [Value] = 1),
    Grouped = Table.Group(Filtered, {"Number"}, {{"Value", each Text.Combine(List.Transform([Attribute], Text.From), ","), type text}})
in
    Grouped

So you can get what you want.

Best Regards

Yilong Zhou

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

Anonymous - The example provided by Anonymous  was just that. The source step contains a static data table that recreates the data supplied in your screenshot. 

You need to replace the code in the source step with a connection to your csv file, this should give you a start on that: https://goanalyticsbi.com/how-to-connect-to-csv-data-in-power-bi-desktop/

Then you need to make the code supplied matches the first column name in your CSV. If your first column is called Number it will work fine, if not, you need to make sure you change "Number" in the steps that begin with Unpivoted = and Grouped =