Forum Discussion
How to Create a New Table from existing table using IN POWER QUERY NOT IN Data model
How to Create a New Table from existing table using EDIT QUERY in POWER BI? IN POWER QUERY NOT IN DATA MODEL
Example :
| Table A | |||
| Name | ID | Amount | Product |
| surya | 111 | 324 | Milk |
| surya | 111 | 32423 | chocolate |
| dinesh | 222 | 52432 | cherry |
| dinesh | 222 | 234324 | orange |
| Prem | 333 | 523423 | apple |
| Vishnu | 444 | 42342 | Juice |
Result (Expected Table) : I want to fetch column 'ID and Name' from table A using DISTINCT Fuction.
| Table B | |
| Name | ID |
| surya | 111 |
| dinesh | 222 |
| Prem | 333 |
| Vishnu | 444 |
It would be great if you help me.
Thanks in Advance!
Hi Anonymous ,
I also use the option "Group By" to do it. You could have a try.
let Source = Table, #"Grouped Rows" = Table.Group(Source, {"Name", "ID"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"}) in #"Removed Columns"
5 Replies
Hi Anonymous do the following to get a table like you want:
- Right-click on Table A and select "Reference"
- In the new query, right-click on the ID column and select Remove Other Columns
- Right-click again on ID column and "Removed Duplicates"
Now you can use this as a DIM table in your model. I do this all of the time, creating DIM tables from FACT tables. 👍
Anonymous
Multiple solutions here, all of them are great, you can also consider
Right click on your table in powerquery ==> Duplicate ==> on this new table click 'Group BY' in the tool bar.
Anonymous - Right-click your first query and select Reference.
Hi Anonymous ,
I also use the option "Group By" to do it. You could have a try.
let Source = Table, #"Grouped Rows" = Table.Group(Source, {"Name", "ID"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"}) in #"Removed Columns"
