Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 11...
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    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"

     

edhans's avatar
edhans
Community Champion
6 years ago

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