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

4 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

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