Forum Discussion
Create a new table based on specific row column data
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...
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 GroupedSo 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 =
6 Replies
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 - 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.
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 GroupedSo 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.
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?
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 =