Ошибка power query dataformat error

• 
  All forum topics

• 
  Previous Topic

• Next Topic
  

2 ACCEPTED SOLUTIONS

• 
  All forum topics

• 
  Previous Topic

• Next Topic
  

• Remove From My Forums

• Question

• I’m having an issue with filtering a specific column to show only empty rows. I’ve tried multiple different fixes, and none seem to work. 

  I’ll paste the whole query below, but the trouble spot is here:  

   #»Filtered Rows» = Table.SelectRows(#»Changed Type», each ([Assigned User] <> null))

  I’ve tried changing to 

   #»Filtered Rows» = Table.SelectRows(#»Changed Type», each ([Assigned User] = null))

  and 

   #»Filtered Rows» = Table.SelectRows(#»Changed Type», each ([Assigned User] is null))

  In both cases when I attempt to load the data, I get the «[DataFormat.Error] We couldn’t convert to Number» pop up. The preview is working on the query; it’s only happening when I attempt to lad. the specific column is a Text Column, and the source
  tables are both set as text as well. 

  I also get this error if I move the filter in the original query to later in the steps. Any ideas? 

  Here’s the full query: 

  let
      Source = Table.NestedJoin(IHIE_MHIN_Combined,{«MRN»},Prod_and_CM_For_Query,{«MRN»},»Prod_and_CM_For_Query»,JoinKind.LeftOuter),
      #»Expanded Prod_and_CM_For_Query» = Table.ExpandTableColumn(Source, «Prod_and_CM_For_Query», {«Product», «Assigned User»}, {«Product», «Assigned User»}),
      #»Reordered Columns» = Table.ReorderColumns(#»Expanded Prod_and_CM_For_Query»,{«Source.Name», «ADMITTYPE», «Product», «Assigned User», «MRN», «LASTNAME», «FIRSTNAME»,
  «DOB», «DATE_OF_DEATH», «SSN», «ADDRESS1», «ADDRESS2», «CITY», «STATE», «ZIPCODE», «ADMIT_DATE», «DISCHARGE_DATE», «CHIEFCOMPLAINT», «DIAGNOSES_CODES»,
  «DIAGNOSES_DESCRIPTIONS», «INSTITUTIONNAME», «ORGANIZATIONFORMALNAME», «LOCATIONFORMALNAME», «LOCATIONDESCRIPTION», «ATTENDINGNAME», «PHONE», «ENCOUNTER_ID», «ATTENDING_NPI»}),
      #»Changed Type» = Table.TransformColumnTypes(#»Reordered Columns»,{{«DOB», type date}, {«DATE_OF_DEATH», type date}, {«ADMIT_DATE», type date}, {«DISCHARGE_DATE», type date}, {«PHONE»,
  type text}, {«ENCOUNTER_ID», type text}}),
      #»Filtered Rows» = Table.SelectRows(#»Changed Type», each ([Assigned User] <> null)),
      #»Inserted Merged Column» = Table.AddColumn(#»Filtered Rows», «Merged», each Text.Combine({[Source.Name], [ADMITTYPE], [Product], [Assigned User], [MRN], [LASTNAME], [FIRSTNAME], Text.From([DOB], «en-US»),
  Text.From([DATE_OF_DEATH], «en-US»), [SSN], [ADDRESS1], [ADDRESS2], [CITY], [STATE], Text.From([ZIPCODE], «en-US»), Text.From([ADMIT_DATE], «en-US»), Text.From([DISCHARGE_DATE], «en-US»), [CHIEFCOMPLAINT], [DIAGNOSES_CODES],
  [DIAGNOSES_DESCRIPTIONS], [INSTITUTIONNAME], [ORGANIZATIONFORMALNAME], [LOCATIONFORMALNAME], [LOCATIONDESCRIPTION], [ATTENDINGNAME], [PHONE], Text.From([ENCOUNTER_ID], «en-US»), Text.From([ATTENDING_NPI], «en-US»)}, «»), type
  text),
      #»Removed Duplicates» = Table.Distinct(#»Inserted Merged Column», {«Merged»}),
      #»Removed Columns» = Table.RemoveColumns(#»Removed Duplicates»,{«Merged»}),
      #»Filtered Rows1″ = Table.SelectRows(#»Removed Columns», each ([Product] <> null))
  in
      #»Filtered Rows1″