Im getting this error with no null values. {We cannot convert the value null to type List}

[jpmakako]

Hi, Ive checked for all columns and I have no null values in any record. Still, while loading into Power Bi Im no being able to load the table. Any ideas?

McDATAMART_Proyeccion_Datos

Failed to save modifications to the server. Error returned: 'Cannot find table 'McDATAMART_Proyeccion_Caja'. Cannot find table 'McDATAMART_Proyeccion_Caja'. Cannot find table 'McDATAMART_Proyeccion_Caja'. The value for 'BLANK()' cannot be determined. Either the column doesn't exist, or there is no current row for this column. OLE DB or ODBC error: [Expression.Error] We cannot convert the value null to type List.. An unexpected exception occurred. '.

[jpmakako]

I have managed to find the particular source of error but fail to understand why.

Long story short: Im have a query that pulls an Excel from my sharepoint. In that file i have several addresses of other excel files in my sharepoint and i merge them. I Power Bi Desktop, I have no issues while refreshing the data, but in refreshing it online automatically i have permit issues. I read in another post that I should refer to each of the excel files individually to unique queries so that the automated refresh runs with no errors. So i created the individual queries to each file.

_QueryFilePR

let
    Source = Excel.Workbook(Web.Contents("https://XXX.sharepoint.com/sites/XXX/XXXPR.xlsx"), null, true)
in
    Source

The thing is that in this code i have a the problem:    

DatamartFD

let
    Source = McDATAMART,
    McFilter = Table.SelectRows(Source, each [FCSharepoint] <> null),
    McGetQuery = Table.AddColumn(McFilter, "Table Query", each Expression.Evaluate([Query Name],#shared)),
    McRemoveOtherCols = Table.SelectColumns(McGetQuery,{"Code",... "XXX"}),
    _McAddTablaProyeccion = Table.AddColumn(McRemoveOtherCols,"ExcelQuery", each Excel.Workbook( Web.Contents([FCSharepoint])){[Item=[Proy],Kind="Table"]}[Data]),
    //_McAddTablaProyeccion = Table.AddColumn(McRemoveOtherCols,"ExcelQuery", each [Table Query]{[Item=[Proy],Kind="Table"]}[Data]),
    _McAddTablaProyeccionX =  Table.AddColumn(_McAddTablaProyeccion,"ExcelQueryX", each [Table Query]{[Item=[Proy],Kind="Table"]}[Data]),
.....
in 
McEndQuery

(I changed the code to shorter names so that is was more readable) As you see, in step McGetQuery i insert each of the first code-queries i have shown for the excel "content" of the individual excel file in a new column "Table Query". There is two steps both called _McAddTablaProyeccion, one of the commented out. The difference is that the first instance shows the table from excel being pulled directly from the sharepoint file, in the second i indirectly refer to it in the queries i individually created from each file. Using either works properly in Power Query. Both tables refer the same file but if i run the code this way (the way shown in the above code), the queries are loaded properly. If I instead use the table i loaded indirectly i get the following error once i close and load: 

So my problem is, if i leave it as the first instance, the report works properly but only in Power Bi Desktop. But if i refer to each individual file and try to refer to it in the aggregation query once i load it it will not load. I have checked all the columns and no null values are shown. 

I also created another step _McAddTablaProyeccionX to compare side by side if the table is not exact due to refreshing issues but i have here the screen capture of both tables:

As you can see, both tables are the same one for Tabla Proyeccion and another Tabla ProyeccionX. Any ideas?

[lbendlin]

Please do not refer to individual Excel files. Instead use the SharePoint Folder connector.  That has multiple advantages, including (but not limited to)

- fewer connections

- no need for gateways (unless you attempt Power Query merges)

- no issues with service refresh

[jpmakako]

Thanks, Already changed it. But Still the same problem. The only way that it works is if I refer the excel directly inside the query. 

let
    Source = McDATAMART,
    McFilter = Table.SelectRows(Source, each [Direccion Flujo de Caja en Sharepoint] <> null and [Caracteristicas Proyeccion] <> null and [Separacion Manual] <> null and [Entregas Manual] <> null and [Tabla Fechas] <> null),
    McGetQuery = Table.AddColumn(McFilter, "Tabla Consulta", each Expression.Evaluate([Nombre Consulta],#shared)),
    McRemoveOtherCols = Table.SelectColumns(McGetQuery,{"Codigo Etapa Sinco", "Proyecto Nombre Sinco", "Direccion Flujo de Caja en Sharepoint", "Caracteristicas Proyeccion", "Separacion Manual", "Entregas Manual", "Tabla Fechas","Tabla Consulta"}),
    
    _McAddTablaProyeccion = Table.AddColumn(McRemoveOtherCols,"Tabla Proyeccion", each Excel.Workbook( Web.Contents([Direccion Flujo de Caja en Sharepoint])){[Item=[Caracteristicas Proyeccion],Kind="Table"]}[Data]),
    //_McAddTablaProyeccion = Table.AddColumn(McRemoveOtherCols,"Tabla Proyeccion", each [Tabla Consulta]{[Item=[Caracteristicas Proyeccion],Kind="Table"]}[Data]),
    
    //_McAddTablaProyeccionX =  Table.AddColumn(_McAddTablaProyeccion,"Tabla ProyeccionX", each [Tabla Consulta]{[Item=[Caracteristicas Proyeccion],Kind="Table"]}[Data]),
    McGetVelocidad = Table.AddColumn(_McAddTablaProyeccion, "Velocidad Ventas",each [Tabla Proyeccion]{5}[Valores],Int64.Type),
    McGetVelocidadEntregas = Table.AddColumn(McGetVelocidad, "Velocidad Entregas",each [Tabla Proyeccion]{7}[Valores] ),
    McGetPeriodoMinimo = Table.AddColumn(McGetVelocidadEntregas, "Periodo Minimo",each [Tabla Proyeccion]{9}[Valores] ),
    
    _McAddTablaSeparacion = Table.AddColumn(McGetPeriodoMinimo , "Tabla Separacion", each Excel.Workbook( Web.Contents([Direccion Flujo de Caja en Sharepoint])){[Name=[Separacion Manual]]}[Data]),
    //_McAddTablaSeparacion = Table.AddColumn(McGetPeriodoMinimo , "Tabla Separacion", each [Tabla Consulta]{[Name=[Separacion Manual]]}[Data]),
    
    _McAddTablaEntregas = Table.AddColumn(_McAddTablaSeparacion , "Tabla Entregas", each Excel.Workbook( Web.Contents([Direccion Flujo de Caja en Sharepoint])){[Name=[Entregas Manual]]}[Data]),
    McPullEntregas = Table.AddColumn( _McAddTablaEntregas,"Promedio Entregas", each Number.RoundUp( List.Average(Table.SelectRows( Table.Transpose(_[Tabla Entregas]), each _[Column1] <> 0)[Column1]))),
    McCorrectEntregas = Table.AddColumn( McPullEntregas,"Correccion Entregas", each if [Velocidad Entregas] = -1 then [Promedio Entregas] else [Velocidad Entregas]),
    
    _McAddTableFechas = Table.AddColumn( McCorrectEntregas, "Tabla Fecha",each Excel.Workbook( Web.Contents([Direccion Flujo de Caja en Sharepoint])){[Name=[Tabla Fechas]]}[Data]),
    //_McAddTableFechas = Table.AddColumn( McCorrectEntregas, "Tabla Fecha",each [Tabla Consulta]{[Name=[Tabla Fechas]]}[Data]),
    
    McRemoveOtherCols2 = Table.SelectColumns(_McAddTableFechas,{"Codigo Etapa Sinco",
        "Proyecto Nombre Sinco", "Tabla Proyeccion", "Velocidad Ventas", "Correccion Entregas", "Tabla Separacion", "Tabla Entregas", "Tabla Fecha"}),
    McInventariodeSinco = Table.Buffer( Table.SelectColumns(Table.SelectRows(#"ADI_DTM Inventarios",each [Mc UnidadPPal]=true and [InvEstUnidad]="Disponible"),{"InvNombreProyecto","InvIdUnidad"})),
    McPullInventario = Table.AddColumn(McRemoveOtherCols2,"Inventario Sinco",(OT)=> List.Count(Table.SelectRows(McInventariodeSinco,each [InvNombreProyecto] = OT[Proyecto Nombre Sinco])[InvNombreProyecto])),
    McFixSeparacion = Table.AddColumn( McPullInventario,"Tabla Separacion Corregida", each FunMcFixaInventario([Tabla Separacion],[Velocidad Ventas],[Inventario Sinco])),
    McFixEntregas = Table.AddColumn( McFixSeparacion,"Tabla Entregas Corregida", each FunMcFixaInventario([Tabla Entregas],[Correccion Entregas],[Inventario Sinco])),
    McAddFunSeparacion = Table.AddColumn( McFixEntregas,"Fun Separacion",each Mc0FunSeparacion([Tabla Separacion Corregida],  [Tabla Fecha])),
    McAddFunEntregas = Table.AddColumn( McAddFunSeparacion,"Fun Entregas",each Mc0FunEntregas( [Tabla Fecha],[Tabla Entregas Corregida])),
    McAddFunProyeccion = Table.AddColumn( McAddFunEntregas,"Fun Proyeccion",each Mc1FunProyeccionVentas( [Tabla Proyeccion],[Fun Separacion],[Fun Entregas],TablaSMMLV, [Inventario Sinco])),
    McClean = Table.SelectColumns(McAddFunProyeccion,{"Codigo Etapa Sinco","Proyecto Nombre Sinco", "Fun Proyeccion"}),
    McExpandProyeccion = Table.ExpandTableColumn(McClean, "Fun Proyeccion", {"NumUnidad", "Flujo"}, {"NumUnidad", "Flujo"}),
    McExpandFlujo = Table.ExpandTableColumn(McExpandProyeccion, "Flujo", {"Fechas", "Separacion", "Credito", "Subsidio", "Cuota Inicial", "Total", "VentasUnd", "VentasM2", "Ventas$", "EscrituraUnd", "EscrituraM2", "Escritura$"}, {"Fechas","Separacion", "Credito", "Subsidio", "Cuota Inicial", "Total", "VentasUnd", "VentasM2", "Ventas$", "EscrituraUnd", "EscrituraM2", "Escritura$"}),
    McChType = Table.TransformColumnTypes(McExpandFlujo,{{"NumUnidad", Int64.Type}, {"Fechas", type date}, {"Separacion", Currency.Type}, {"Credito", Currency.Type}, {"Subsidio", Currency.Type}, {"Cuota Inicial", Currency.Type}, {"Total", Currency.Type}, {"VentasUnd", Int64.Type}, {"VentasM2", type number}, {"Ventas$", Currency.Type}, {"EscrituraUnd", Int64.Type}, {"EscrituraM2", type number}, {"Escritura$", Currency.Type}})
in
    McChType

 Here i put the whole query. I inserted an undescore between those steps that only work if i access the Excel directly but shows me the following error if i select the one commented out instead of the other.

It is worth mentioning the above code is used in Query Datamart, the error shown below happens in two depending queries.

McDatamart_Proyeccion_Caja shows null values but none appear: Here i post a profile of columns based on the whole dataset

Also, McDatamart_Proyeccion:Datos depends on the DATAMART query.

[lbendlin]

I don't see the change

each Excel.Workbook( Web.Contents([Direccion Flujo de Caja en Sharepoint])){[Name=[Entregas Manual]]}[Data]),

[jpmakako]

_McAddTableFechas = Table.AddColumn( McCorrectEntregas, "Tabla Fecha",each Excel.Workbook( Web.Contents([Direccion Flujo de Caja en Sharepoint])){[Name=[Tabla Fechas]]}[Data]),
//_McAddTableFechas = Table.AddColumn( McCorrectEntregas, "Tabla Fecha",each [Tabla Consulta]{[Name=[Tabla Fechas]]}[Data]),

If i run it this way, is works, If i comment the above statement and uncomment the lower one power query shows no errors but power bi wont load it. I hope that clarifies the code.

[lbendlin]

Please provide more details. Did you make meta data changes without then updating the app?