Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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"
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"
@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.
Hi @Anonymous do the following to get a table like you want:
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. 👍
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingafter loaded your tabIN, create a new blank query under NewSource tools.
Then load the columns you want to operate on :
and finally remove duplicate (a tool under remove rows group):
and you get what you want:
@Anonymous - Right-click your first query and select Reference.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.