Summarize (error data point); Grouping

[ggatza123]

Hi All,

New to the Power BI world; I've lived in Excel but am learning of the great ways these two programs can compliment each other.

I have an issue with my data, here is an example:

Order Number	Order Line	Amount	Quantity	Date	Part	Representative
6450	1	100	5	5/1/2018	123456789 B
6450	1	-50	5	5/1/2018	123456789 B	ABC
6450	1	-50	5	5/1/2018	123456789 B	DEF
6450	1	50	5	5/1/2018	123456789 B	DEF

Desired result:

Order Number	Order Line	Amount	Quantity	Date	Part	Representative
6450	1	50	5	5/1/2018	123456789 B	DEF

The issue is how the data is written at the source and how the source treats modifications to the data.  The "Representative" is always the last representative that is listed in the data.  Meaning if a line on an order is 143 rows long, the 143rd row is the defining datapoint.

When I create reports (by Representative) I want to make sure the whole order is represented on a specific representative.

I've looked into creating a column with logic, using the "Last" function within the data point on the report, or dabbled into Grouping under the Edit Query module.

Any help is greatly appreciated.

[Greg_Deckler · Accepted solution]

Add an Index column to your import query. Then you can always use the MAX of that to determine the row that you want.