Forum Discussion

masplin's avatar
masplin
Icon for Impactful Individual rankImpactful Individual
4 years ago
Solved

Filed contents disappearing ????

I have this data in my source row

 

 

I then add a filtere on the account no and the title and gender disappear?

 

 

Code is

let
    Source = Table.Combine({#"Client Dynamics", #"Client Staffplan"}),
    #"Filtered Rows2" = Table.SelectRows(Source, each [Account Number] = "66023822"),

 

Weidly if I first filter out any rows with null last name then do the filter its fine???

let
    Source = Table.Combine({#"Client Dynamics", #"Client Staffplan"}),
    #"Filtered Rows3" = Table.SelectRows(Source, each ([Last Name] <> null)),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows3", each [Account Number] = "66023822"),

 

 

Makes absolutely no sense??? Can anyone explain?

 

Much appreciated

 

Need Help
  • masplin's avatar
    masplin
    4 years ago

    Ok I found it but it makes no sense.

     

    The data i'm using is coming form microsoft Dynamics.  There are 2 fields cha_gender which is a numerical field 12664000 which means male. There is another field cha_gender_display which shows the value of this number as male.  If I only hav ethe display field in thetable then sorting/filtering wipes out thew value.  If I have both fields it doesn't. Same for al lfields that are stored as numbers, but have text display values. 

     

    Its as if sorting/filtering is forcing a relokup but becuase the numericla field is in the data it returns a null i.e. the text i'm seeing is not actually a real piece of data. As long as i remove the numericla fields right at the end it looks ok. Data is coming ofrm a CDS feed

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    You sure you don't have a duplicate in there? I would change my types before filtering, and then trim, and then check for duplicates before filtering. 

    • masplin's avatar
      masplin
      Icon for Impactful Individual rankImpactful Individual
      4 years ago

      I have double checked and only one row with this account number in the source

       

      Thisis the view at source level. You can see Mr Ainscow at the bottom

       

       

      Now just filter for his account number

       

       

       

      It seems sorting or filtering the source just wipes out these fields

      • masplin's avatar
        masplin
        Icon for Impactful Individual rankImpactful Individual
        4 years ago

        Ok I found it but it makes no sense.

         

        The data i'm using is coming form microsoft Dynamics.  There are 2 fields cha_gender which is a numerical field 12664000 which means male. There is another field cha_gender_display which shows the value of this number as male.  If I only hav ethe display field in thetable then sorting/filtering wipes out thew value.  If I have both fields it doesn't. Same for al lfields that are stored as numbers, but have text display values. 

         

        Its as if sorting/filtering is forcing a relokup but becuase the numericla field is in the data it returns a null i.e. the text i'm seeing is not actually a real piece of data. As long as i remove the numericla fields right at the end it looks ok. Data is coming ofrm a CDS feed