Create a new table based on specific row column data

[Anonymous]

Please help! We are trying to pull individual row/column data from a dataset and put those rows into a new table

1) Get a listing of data from the original dataset (this data represents two joined tables) 

2) Refine the output from step 1 to flatten it as shown below in the image:

3) Create a new table with Step 2 data and filter out any that do not have 'x' in each row/column  as shown below:

4) get rid of empty rows... 

[Anonymous · Accepted solution]

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 · Accepted solution]

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]

Thank you so much Yilong!  This is closer to what we are trying to get but the numbers change (based on the csv sheet that is incoming) and there are nearly 50 columns and hundreds of rows in the data. How would I make this more dynamic please? Can some sort of IF or Switch be used to test the values in each row/column combination? 

[mark_endicott · Accepted solution]

@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 =

[mark_endicott]

@Anonymous - This isn't a problem for DAX to fix. This is best off done in Power Query. 

I suggest you reveiw this video as there is likely to be some guidance as to how you can solve this challenge easily:

https://www.youtube.com/watch?v=HAfpyY2OvZQ

If this helps, please accept as the solution so others with the same challenge can find the answer. 

[Anonymous]

That video does not help - he is only truncating tables together.  We need to create a new table based on the 'x' showing up in row/column combination as shown in the pictures... 

[mark_endicott]

@Anonymous - I was just trying to show you some of the techniques you would need to use, so you couldlear. Maybe I used the wrong video. 

@Anonymous has given you a great solution, so go with that.