July 11, 2016
Is it possible to add a custom column in PQ that looks at the values in another column and returns a value of 1 the first time the value is encountered but a value of 0 for any further instances?
For example I have a column that contains sales quotation numbers. There are multiple lines for each quotation so the quotation number is replicated on each line. In the custom column I just want to show 1 when the first line is counted but zero for the remaining lines. When I sum up the values in the custom column it will give me the unique number of quotes raised.
I know that I could achieve this by grouping rows or isolating the column and removing duplicates but ideally I would like to do it by adding a column so I can retain all of the rest of the data in the table.
July 16, 2010
This type of analysis is something you do in Power Pivot using the DISTINCTCOUNT function. That said, if you really want to do it with Power Query then you could use the technique described here: https://www.myonlinetraininghu.....ower-query and then add a column that says if the 'Sub Area No.' is > 1 then 0 else 1.