How to Create a New Table from existing table using IN POWER QUERY NOT IN Data model

[Anonymous]

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!

[v-xuding-msft · Accepted 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"

 

[mhossain]

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

[edhans]

Hi @Anonymous do the following to get a table like you want:

1. Right-click on Table A and select "Reference"
2. In the new query, right-click on the ID column and select Remove Other Columns
3. 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]

after 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:

[Greg_Deckler]

@Anonymous - Right-click your first query and select Reference.