Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Keeping column values when another field is blank

Hello,   I'm having a problem where Power BI is changing some of my column values to 'null' when there are actually numbers in the columns - it is doing this where other columns on that row are bla...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks, parry2k 

 

Uploaded a sample of the csv data source here https://ufile.io/2nzfh 

where columns C and D contain some blank fields that cause the following number columns to turn null in Power BI.

 

Advanced editor contains (with some edits for sensitive information)

let
Source = Csv.Document(File.Contents("L:\FILELOCATION"),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"dated", type datetime}, {"subject", type text}, removed a column for sensitity here (same as csv sample), {"utm_source2", type text}, {"utm_medium2", type text}, {"utm_campaign", type text},{"document_type", type text}, {"Actions taken", Int64.Type}, {"Action type 2", Int64.Type}, {"Action type 2", Int64.Type}, {"Actions taken all time", Int64.Type}, {"week", type date}, {"month", type date}, {"year", type date}})
in
#"Changed Type"

 

Thanks in advance for taking a look

parry2k's avatar
parry2k
Super User
7 years ago

Anonymous i just connected to csv file you provided and don't see any blank, pbix is attached.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Parry,

     

    That's right, there are no blanks in the columns F-I however when loaded to Power BI, it created these nulls in the screenshot above. I have looked into the original data csv again and discovered the rows that are effected when imported to Power BI actually aren't blank - they contain three returns (paragraphs), so they don't appear in excel as (blanks) but " " instead. Is it possible these returns in the cells are creating additional null and error rows in Power BI, and discounting the rest of the fields in the same row?

     

    Perhaps a solution would be where the cell contains " " (returns) to count as blank? Not sure how I would query for that in a formula.

     

    Hope that makes sense.

     

    Thank you for your help so far

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thought I would reply that I have discovered the error. Null rows were appearing in Power BI where there existed return carriages in any cell within the csv file.

       

      Thanks for your help!

      • parry2k's avatar
        parry2k
        Super User
        7 years ago

        Anonymous appreciate the update, there is always something. Glad issue is resolved. Cheers!!