none
[DataFormat.Error] Invalid cell value '#N/A' RRS feed

  • Question

  • Hi team,

    Today I really got a big headache of " Invalid cell value " error.

    I got a table with 44 small tables and each small table look like this:

    Each of these small tables get same structure and all the data types are correct and without any errors

    However, when I try to expand these small tables and make them into a big table, I got the error message:

    [DataFormat.Error] Invalid cell value '#N/A'.

    I tried to use remove error function or replace error function while those ways are dead end and refuse to work.

    Where does the error come from and how can I fix it?

    The following query is the code before expand table and it works without anything wrong:

    let
        Source = Folder.Files("C:\Users\t.sun1\Desktop\Bstar\sellout"),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
        #"Uppercased Text" = Table.TransformColumns(#"Expanded Custom",{{"Kind", Text.Upper}, {"Item", Text.Upper}}),
        #"Filtered Rows" = Table.SelectRows(#"Uppercased Text", each [Kind] = "SHEET"),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Item] = "SAMSUNG"),
        #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Data"}),
        tb=(x)=>
        let
        ntb=Table.TransformColumnTypes(x,List.Transform(Table.ColumnNames(x),each {_,type text})),
        dt=List.Select
        (
        List.Transform
        (
        Record.ToTable(ntb{0})[Value],
        each try Date.From(_) otherwise null
        ),
        each _ <> null
        ),
        ud=List.Max(dt),
        cn={"Product Group","OEM Device", "Customer"} & List.Transform(dt, each Text.From(_)),
        ntb1=Table.PromoteHeaders(ntb),
        ntb2=Table.SelectRows(Table.SelectColumns(ntb1,cn), each [OEM Device]<>null),
        ntb3=Table.TransformColumns(ntb2,{{"Product Group", Text.Upper}, {"OEM Device", Text.Upper}, {"Customer", Text.Upper}}),
        dt1=
        List.Transform
        (
        List.Transform(dt,each Text.From(_)),
        each {_,Int64.Type}
        ),
        ntb4=Table.TransformColumnTypes(ntb3,dt1),
        ntb5=Table.UnpivotOtherColumns(ntb4, {"Product Group", "OEM Device", "Customer"}, "Date", "Sales"),
        ntb6=Table.TransformColumnTypes(ntb5,{{"Date", type date}, {"Sales", Int64.Type}}),
        ntb7=Table.AddColumn(ntb6,"Update Date",each ud),
        ntb8=Table.TransformColumnTypes(ntb7,{"Update Date",type date})

        in
        ntb8,
        nc=Table.AddColumn(#"Removed Other Columns1","NTB",each tb([Data])),

    ''This line above is the procedure to setup 44 small table listed in above picture

        #"Removed Other Columns2" = Table.SelectColumns(nc,{"NTB"}),
        #"Expanded NTB" = Table.ExpandTableColumn(#"Removed Other Columns2", "NTB", {"Product Group", "OEM Device", "Customer", "Date", "Sales", "Update Date"}, {"NTB.Product Group", "NTB.OEM Device", "NTB.Customer", "NTB.Date", "NTB.Sales", "NTB.Update Date"})

    '' This line is the expand line and after this line get the error message when I choose a column and click "load more" button

    in
        #"Expanded NTB"

    Cheers,

    Tom Sun



    • Edited by Tom J Sun Thursday, February 2, 2017 1:02 AM edit
    Thursday, February 2, 2017 12:57 AM

Answers

  • I spent 2 hours on this issue and finally worked out what happened.

    In one column of a table there is a value listed as #N/A, which is not accepted by power query.

    After changing this to something else, the query worked.

    Just a tip for me that some special value is not accepted by power query and they are going to bring big headaches to the process.

    Hope this can help someone else.

    Cheers,

    Tom Sun

    Thursday, February 2, 2017 3:17 AM

All replies

  • I spent 2 hours on this issue and finally worked out what happened.

    In one column of a table there is a value listed as #N/A, which is not accepted by power query.

    After changing this to something else, the query worked.

    Just a tip for me that some special value is not accepted by power query and they are going to bring big headaches to the process.

    Hope this can help someone else.

    Cheers,

    Tom Sun

    Thursday, February 2, 2017 3:17 AM
  • "Just a tip for me that some special value is not accepted by power query and they are going to bring big headaches to the process."

    I don't think the problem is as simple as you stated. It's probably something that occurred during the table expansion. For example, on an Excel table, if you have values that are #NA, these values show up as errors when the table is imported into PQ. In PQ, you can set a value as Number.NaN(), and the value would show up in Excel as #NA.

    The reason for your problem is yet to be explained.

    Thursday, February 2, 2017 4:56 PM
  • Hi Colin,

    Thanks for your reply.

    The original value from the data source is #N/A and this value has been picked up by pq.

    However, once I changed the value from data source to N/A, pq did not pick that up and everything worked.

    I will try your way and to see whether it worked or not.

    Cheers,

    Tom Sun

    Thursday, February 2, 2017 9:45 PM
  • I ran into this recently...

    So, the problem is an Excel issue, in the Excel data connector. In Excel, #N/A is a reserved value for errors. 

    If you type up a list in Excel of words like Cat, Dog, etc, and then type #N/A on a line, you will notice that all of the words left justify, but that #N/A shows a special indentation, indicating that it is an error value and not a text value. 

    If you save the file as a .csv file, you will see that the #N/A will left justify as text. Power Query can get this value as text because the .csv connector does not see #N/A as an error value. 

    Saturday, July 22, 2017 4:21 PM
  • Thank you- you saved me time. Where can i send a free beer? :)
    Monday, August 21, 2017 8:39 AM
  • Just send it to Microsoft as they did provide the best software to us.
    Monday, August 21, 2017 9:01 AM