Forum Discussion
OLE DB or ODBC error: [Expression.Error] We cannot convert the value "" to type Table.
HI! I have a problem with power bi desk
let
Source = Folder.Files("C:\Users\Denise.Scandalitta\OneDrive - Unilever\Documentos\Argentina\Impuesto Gcias\2023\Ajuste anual IGG\F572\16-07-2024 F572 2023"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Expanded empleado1" = Table.ExpandTableColumn(#"Expanded Table Column1", "empleado", {"cuit", "tipoDoc", "apellido", "nombre", "direccion"}, {"cuit", "tipoDoc", "apellido", "nombre", "direccion"}),
#"Expanded direccion" = Table.ExpandTableColumn(#"Expanded empleado1", "direccion", {"provincia", "cp", "localidad", "calle", "nro"}, {"provincia", "cp", "localidad", "calle", "nro"}),
#"Expanded ganLiqOtrosEmpEnt" = Table.ExpandTableColumn(#"Expanded direccion", "ganLiqOtrosEmpEnt", {"empEnt"}, {"empEnt"}),
#"Expanded empEnt" = Table.ExpandTableColumn(#"Expanded ganLiqOtrosEmpEnt", "empEnt", {"cuit", "denominacion", "ingresosAportes", "convenioColectivo", "transporteLargaDist", "transporteTerrLargaDist"}, {"cuit.1", "denominacion", "ingresosAportes", "convenioColectivo", "transporteLargaDist", "transporteTerrLargaDist"}),
#"Expanded ingresosAportes" = Table.ExpandTableColumn(#"Expanded empEnt", "ingresosAportes", {"ingAp"}, {"ingAp"}),
#"Expanded ingAp" = Table.ExpandTableColumn(#"Expanded ingresosAportes", "ingAp", {"obraSoc", "segSoc", "sind", "ganBrut", "retGan", "retribNoHab", "ajuste", "exeNoAlc", "sac", "horasExtGr", "horasExtEx", "matDid", "gastosMovViat", "bonosProd", "fallosCaja", "conSimNat", "remunExentaLey27549", "suplemParticLey19101", "teletrabajoExento", "Attribute:mes", "movilidad", "viaticos"}, {"obraSoc", "segSoc", "sind", "ganBrut", "retGan", "retribNoHab", "ajuste", "exeNoAlc", "sac", "horasExtGr", "horasExtEx", "matDid", "gastosMovViat", "bonosProd", "fallosCaja", "conSimNat", "remunExentaLey27549", "suplemParticLey19101", "teletrabajoExento", "Attribute:mes", "movilidad", "viaticos"}),
#"Expanded deducciones" = Table.ExpandTableColumn(#"Expanded ingAp", "deducciones", {"deduccion"}, {"deduccion"}),
#"Expanded deduccion" = Table.ExpandTableColumn(#"Expanded deducciones", "deduccion", {"tipoDoc", "nroDoc", "denominacion", "descBasica", "montoTotal", "periodos", "Attribute:tipo"}, {"tipoDoc.1", "nroDoc", "denominacion.1", "descBasica", "montoTotal", "periodos", "Attribute:tipo"}),
#"Expanded periodos" = Table.ExpandTableColumn(#"Expanded deduccion", "periodos", {"periodo"}, {"periodo.1"}),
#"Expanded periodo.1" = Table.ExpandTableColumn(#"Expanded periodos", "periodo.1", {"Attribute:mesDesde", "Attribute:mesHasta", "Attribute:montoMensual"}, {"Attribute:mesDesde", "Attribute:mesHasta", "Attribute:montoMensual"})
in
#"Expanded periodo.1"
Hi DS_30 ,
According to your statement, I think your issue should be casued that some data are not Table format.
So if you use Table.ExpandTableColumn function, Power Query will only expand the tables and empty value will return error.
I think you can try to duplicate your table. Then filter one table to let it show all tables and expand it. Filter another table and let show all other not table data. Finally, use Append function to combine two tables.
For reference: Append queries - Power Query | Microsoft Learn
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi DS_30 - I think the issue with the way a step is returning data.
can you please share the sample data or snapshot of the issue that your facing?
Hope it helps
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!Hi DS_30 ,
According to your M code, I think you are trying to get data from a local folder, selecting the files, invoking and do some transformation.
This error may be cause by converting to table as rajendraongole1 mentioned.
I suggest you to do troubleshooting step by step. For example, you can check whether you can load data successfully in #"Invoke Custom Function1" step, then check bellow steps and find the error.
If you still couldn't find the solution, please share a sample file with us. This will make it easier to find the solution.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi how are you?
The error appear when i try to go on with this step:
= Table.ExpandTableColumn(#"Expanded ganLiqOtrosEmpEnt.empEnt", "ganLiqOtrosEmpEnt.empEnt.ingresosAportes", {"ingAp"}, {"ganLiqOtrosEmpEnt.empEnt.ingresosAportes.ingAp"})
Maybe the issue is because some data of this columns are not table?
Do you know how can i resolve this problem?
Hi how are you?
The error appear when i try to go on with this step:
= Table.ExpandTableColumn(#"Expanded ganLiqOtrosEmpEnt.empEnt", "ganLiqOtrosEmpEnt.empEnt.ingresosAportes", {"ingAp"}, {"ganLiqOtrosEmpEnt.empEnt.ingresosAportes.ingAp"})
Maybe the issue is because some data of this columns are not table?
Do you know how can i resolve this problem?
Hi how are you?
The error appear when i try to go on with this step:
= Table.ExpandTableColumn(#"Expanded ganLiqOtrosEmpEnt.empEnt", "ganLiqOtrosEmpEnt.empEnt.ingresosAportes", {"ingAp"}, {"ganLiqOtrosEmpEnt.empEnt.ingresosAportes.ingAp"})
Maybe the issue is because some data of this columns are not table?
Do you know how can i resolve this problem?
Hi DS_30 ,
According to your statement, I think your issue should be casued that some data are not Table format.
So if you use Table.ExpandTableColumn function, Power Query will only expand the tables and empty value will return error.
I think you can try to duplicate your table. Then filter one table to let it show all tables and expand it. Filter another table and let show all other not table data. Finally, use Append function to combine two tables.
For reference: Append queries - Power Query | Microsoft Learn
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
