Forum Discussion

cz1750's avatar
cz1750
Frequent Visitor
8 years ago
Solved

Validating excel source is in numerical order

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 col...
Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    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.

cz1750's avatar
cz1750
Frequent Visitor
8 years ago

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's avatar
v-xjiin-msft
Solution Sage
8 years ago

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.