topic Create a new table based on specific row column data in DAX Commands and Tips

Create a new table based on specific row column data

Anonymous — Tue, 07 Jan 2025 16:18:21 GMT

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...

Re: Create a new table based on specific row column data

mark_endicott — Tue, 07 Jan 2025 16:52:45 GMT

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.

Re: Create a new table based on specific row column data

Anonymous — Tue, 07 Jan 2025 18:52:08 GMT

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...

Re: Create a new table based on specific row column data

Anonymous — Wed, 08 Jan 2025 06:09:20 GMT

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.

Re: Create a new table based on specific row column data

mark_endicott — Wed, 08 Jan 2025 09:17:45 GMT

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.

Re: Create a new table based on specific row column data

Anonymous — Wed, 08 Jan 2025 13:13:55 GMT

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?

Re: Create a new table based on specific row column data

mark_endicott — Wed, 08 Jan 2025 14:31:58 GMT

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 =