New table using M Query

Power Query
1

How to create a new table and retrieve distinct values from an existing column in another table using M Query?

There are not many courses around that teach M Query to an advanced level, so I am wondering how best to learn the M language?

Thank you!

data_PBI.zip (60.2 KB)

2

Hi @powerbideveloper,

You can use the Table.Distinct M function it requires a table and returns a table.

Now if you input a single column from a table that will return a type List, to return a type Table just add another set of square brackets.

Your Query will look something like:

Source = Table.Distinct(#"My query[[MyColumnName]])
.
I hope this is helpful.

1 Like
3

Thanks Melissa,

I guess I should have asked the more complex version from the start, which is how to do this same thing for more than one column and perhaps also for more than one table. I’m guessing that one can use the same code batch for more than one table?

Thanks!

4

Hi

Have you tried the steps explained in this video by Sam McKay?

[Understanding M Code](

Understanding M code | Enterprise DNA Online

)

Regards

Najah

1 Like
5

Unable to fully automate this process but this is what I’ve got for you:

  1. Turned your Table into a List containing a List of each Column
    image
  2. Added a Column where I transformed each List into a List that only contains distinct values
    image
    image1114Ă—254 9.99 KB
  3. Now here’s the part that I haven’t been able to automate, next for each ListDistinct List value I had to add that as a new query
    image
    image756Ă—352 77.6 KB

You only have to set it up once but I’m hoping you don’t have to split up 50, 20 wide column tables…

I hope this was helpful.
eDNA - New table using M Query.pbix (56.1 KB)

1 Like
6

Hi @powerbideveloper

Slightly different approach from Melissa . PFA the solution.

eDNA - New table using M Query_Ankit.pbix (62.8 KB)

  1. This will give Tables for each Columns instead of Lists.
  2. Then you can either use “Add as New Query” or simply extract the required Table using
    Source = Table1{0}[Distinct]

Regarding Step 3 to create individual queries from List or Table, don’t think is possible to automate.

Regards
Ankit J

1 Like
7

Najah
Yes, I’ve gone through the video and it provides great tips, thanks.

Melissa
This is very helpful, so thanks for your great input. I will see if I can take it further.

Ankit
Yours is also helpful, so thanks for your input. I like that they are in tables.

You have all shown me a way to do this so thank you all for such great help. I am not sure if all your suggestions can be marked as the solution, so I’ll have to choose the one that came first.

1 Like