I am using Power Query under "Get & Transform" to work with a large dataset (1.4M rows). I am getting stuck when trying to add a conditional column that breaks down the data by category using greater than or less than statements.
An example of the categories I wish to create are ">100", ">= 75", ">= 50", "<50", etc. I am able to do this with the conditional column tool in the "Power Query Editor", the problem I run into is when I view the data in PowerPivot. The data from the statement above looks correct in the Power Query Editor, but is skewed in the PowerPivot table - where the row totals for a distinct count do not equal the distinct count under the grand total. I am guessing that a unique number that fits both >=50 and >= 100 are being double counted, but I have no idea how to correct this.
I have been researching and have found mixed answers, no solution yet. Any suggestions?