Adding custom columns using List.Distinct
Results 1 to 2 of 2

Thread: Adding custom columns using List.Distinct
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding custom columns using List.Distinct

    https://1drv.ms/x/s!AvjBsEPEq12ngSB0...kOLJz?e=Jfo3wo

    Hey guys,

    I am trying to reduce my first table (where I have my data) into the desired output which shows information for unique values in Column C.

    In Power Query, I have Grouped By the first four columns.
    In the Group By popup menu, I have added one column which does COUNT

    After that, I am using this formula on ADD CUSTOM COLUMN option to show unique list for each other column (from Column E to Column K)

    i.e.
    List.Distinct(Table.Column([Count], "Creation Date"))
    List.Distinct(Table.Column([Count], "PO VAT Amount"))
    List.Distinct(Table.Column([Count], "Total PO Amount"))
    and so on.

    But I am doing this one at a time for each column.
    Is there a quicker way to do this or does it have to be done individually?

    And for the invoice highlighted in orange. How do I make it so that I don't get this result:
    https://imgur.com/a/4kTzM29
    That is, if there are values in Columns I to Columns K for an invoice, I do not need the other rows for that particular invoice.
    Last edited by danhendo888; Jul 27th, 2019 at 07:25 AM.

  2. #2
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding custom columns using List.Distinct

    Quote Originally Posted by danhendo888 View Post
    https://1drv.ms/x/s!AvjBsEPEq12ngSB0...kOLJz?e=Jfo3wo

    And for the invoice highlighted in orange. How do I make it so that I don't get this result:
    https://imgur.com/a/4kTzM29
    That is, if there are values in Columns I to Columns K for an invoice, I do not need the other rows for that particular invoice.
    For this question, I suppose I could create a copy of the table in a new query and then merge them with Left.Outer for values in Columns I to K?
    If my second table had two columns where the first column was the Order Ref. Then the merge would produce values in the second column.
    How would I do it when I need to lookup values for more columns? Is there a short way or do have to do this one by one?
    I.e. create three different table with two columns where the second columns are made of values in Columns I to K, respectively
    Last edited by danhendo888; Jul 27th, 2019 at 07:47 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •