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,

 

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'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.