Validating excel source is in numerical order

[cz1750]

Hi,

I was wondering if it's possible to check items loaded from a excel source to validate that they have been entered in numerical order as expected (1,2,3,4...)

I.e set flag to "Error" and coloured red if the following occurred in the excel source data, as listed below, where id 4 was entered before id 3

Id.
1
2
4
3


Thanks

J

[v-xjiin-msft]

Hi @cz1750,

Why did you need to this?

Sometimes when load data from excel to Power BI. The source may be ordered by other columns like text strings or other columns. It doesn't mean this is wrong. We just can't define a default order for the source for now when loading data. And if you have a numerical column like Id, you can simply re-order the source based on this Id column. 

I don't think it is necessary to do the validation.

Thanks,
Xi Jin.

[cz1750]

Hi,

Thanks for your reply

The reason I need to ensure that the ids are i n order is that there are wightings applied to item 1,2,3,4 etc - so if the ids arein in order it means they applied the wrong weighting to the item

i.e.

correct data where the ids are in the correct order and the relevant weighting is correct thats applied to it

id      weighting

1      <weighting 1st item>

2      <weighting 2nd item>

4      <weighting 3rd item>

7      <weighting 4th item >

But if an id is entered in te wrong order then the wrong weighting is being recorded/applied to it (for id 4 and 3 below - they will bother result in having the wrong weighting applied due to the bein entered in the wrong order)

id      weighting

1      <weighting 1st item>

4      <weighting 2nd item>

2      <weighting 3rd item>

7      <weighting 4th item >

Thanks

J

[cz1750]

Hi,

To  mkae the ordering better i have added a copumn callled grouping to group associated valuse and a Ordering column to specify what was entered 1st, 2nd, 3rd and 4th

Grouping                id      Ordering     weighting

Group1                    1        1               <weighting 1st item>
Group1                    2        2               <weighting 2nd item>
Group1                    4        3               <weighting 3rd item>
Group1                    7        4               <weighting 4th item >

Group2                    1        1               <weighting 1st item>
Group2                    2        2               <weighting 2nd item>
Group2                    7        3               <weighting 3rd item>
Group2                  10       4                <weighting 4th item >

So the previous values would be set as follows

Grouping                id      Ordering     weighting                        Previous Value

Group1                    1        1               <weighting 1st item>      NULL
Group1                    2        2               <weighting 2nd item>    1
Group1                    4        3               <weighting 3rd item>     2
Group1                    7        4               <weighting 4th item >    4

Group2                    1        1               <weighting 1st item>     NULL
Group2                    2        2               <weighting 2nd item>    1
Group2                    7        3               <weighting 3rd item>     2
Group2                  10       4                <weighting 4th item >    7

Thanks

J

[v-xjiin-msft · Accepted solution]

Hi @cz1750,

Sorry for the delay.

Yes, if you got a sequence column, it will be better for the ordering. Please refer to following sample:

The source data in Excel is like:

After loading it into Power BI, it displays as:

Not the right sequence

Then you can use following calculated column to verify the previous value.

Previous Value =
CALCULATE (
    MAX ( Sheet1[id] ),
    FILTER (
        ALL ( Sheet1 ),
        Sheet1[Ordering]
            = EARLIER ( Sheet1[Ordering] ) - 1
            && Sheet1[Grouping] = EARLIER ( Sheet1[Grouping] )
    )
)

As you can see, even though the ordering is not right. The previous value is still returning the expected result.

Thanks,
Xi Jin.