Power Query error import empty table

[PhilippeMuniesa]

Hello everyone, and a very good year 2021.

I work with Excel and PowerQuery.

At first, I retrieve a TXT file ( accounting balance) that ventilated in several tables according to the characteristics  ( each account's audit cycle) of the records with a statement.

let Source - (x) - > let

Source - Excel.CurrentWorkbook()[Name BAL_G"[Content],

Filtre_Cycle = Table.SelectRows (Source , each ([Cycle] - x))  // x  is a setting from A to K plus X for unidentified accounts

In

Filtre_Cycle In Source.

When none of the records meet the selection criteria, PowerQuery returns me an empty table in the Destination Excel sheet.

My binder is thus made up of about  12 sheets, each corresponding to one of the basic criteria (x = Audit Cycles).

Subsequently, I want to retrieve each of these sheets in an independent XL File, 

(**)

let
   Source = Excel.Workbook(File.Contents(Dossier_Travail & "cycles.xlsm"), null, true),
   BAL_G_Table = Source{[Item=Nom_de_Cycle,Kind="Table"]}[Data],  // Item is name of the table corresponding to the Cycle to import
   ....

....

....

in
#"Type modifié"

but in the case of an empty table, PowerQuery generates an error returned in EXCEL.

For example, when there are no unrecognized accounts, the CycleX 's  sheet contains an empty table also named CycleX and the error message is as follows:

" Expression.Error: The key did not match any line in the table. Details:

Key= 

Item-Cycle_x

Kind=Table

Table [Table]"

, and the execution of the demand is interrupted.

I tried various solution, including with try .. otherwise, but even in this case, power query also generates an error:

Expression.Error: Sorry... We were unable to convert the null value to Table type.

Details:

Value Type[Type]

. Is there  a solution to manage errors in the event of importation into thePowerQuery from an empty table ?.

I've bypassed the problem with a few lines of VBA,

..

..

On Error GoTo efface_tx
Sheets(Range("Cycle_ident").Value).ListObjects(1).QueryTable.Refresh BackgroundQuery:=False '//calls the power query query above (**)

End If
efface_tx:
'lorsque la table est vide, excel génère une erreur à l'importation....
Sheets(Range("Cycle_ident").Value).Range("Cycle").Offset(1, 0).EntireRow.Delete  'Query 

End Sub

but I think there's another solution, to get an empty table when you try to import a table with 0 row ?.

Thank you for the leads you can give me.

PS. excuse my English, (French user)

Philippe 

[Jimmy801 · Accepted solution]

Hello @PhilippeMuniesa 

I'm not able to reproduce you scenario. I generated a table in power query and then filtered on a column where an empty table is returned. When loading this to Excel, no error is generated but only an empty table. 

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"A", type text}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each ([A] = "Z"))
in
    #"Gefilterte Zeilen"

Could it be that your SelectRows-statement is something wrong

Table.SelectRows (Source , each ([Cycle] - x)) 

noarmaly you need an equation-sign and then your statement should look like this

Table.SelectRows (Source , each ([Cycle] = x)) 

whereas the x has to be of the same type as the column. 

Just one thing out of curiosity. Why you need to read a TXT-file and split it into 12 sheets? What is the final goal of this? If this is what you really need however it's better to load to your data into your datamodel and then queriying your datatable with dax to different sheets. But first I'm curious whats the goal of having 12 sheets. 🙂

BR

Jimmy

[PhilippeMuniesa]

Jimmy,

thank you for your help.

To answer your question, if I left in twelve sheets, it is because, in order.

-1 I get the balance from a txt file, and assign to each account the letter of the cylinder which corresponds to it by merging with a table corresponding to a standard chart of accounts. the accounts not found are assigned to cycle X.

As you do I use selectRows, and have no mistakes.

- 2 this 1st table is returned to an Excel table so that the user can modify if he wishes the letters of the cycles and mainly the X of the unrecognized accounts..

- 3 Following this user intervention, I reload the excel table in powerquery and i generate with selectrows as you do, the twelve sheets, one of which may look like your table A. The point (avantage)  of the 12 sheets is that the user can browse the binder and view the result (on a scale of 800 accounts, it's easier than filter 12 times a single table). then the file is saved.

then it is on the occasion of another step that I recover each of the 12 tables in 12 independent workbooks, which will be the audit workbooks of the cycles that can be distributed among the different collaborators of the audit team. Regardless of the X cycle which in principle is empty after the user's intervention, it is possible that other cycles are empty (no account corresponding to the cycle), and in this case,The sheet should have an empty table.

It is during the recovery of these tables that I have an error. Following your response I re-examined my code and realized that I made a mistake and called my cycle "Cycle_x", while the excel table that contains the table is named Cycle_X, I modified and I have no more errors. Excuse me for the time wasted.

Thank for your help

Best Regards

Philippe

[StefanoGrimaldi]

maybe make power query when identify its a empty table to change source to a manual input table for the source and make it to imput a empty table.