Total rows in a table as input for PQ how to deal with it?

[Bart Titulaer]

I am using an Excel Table that the user can modify as input for a Power Query. The result is also part of the workbook. The user can not only alter data (allowed) but also add a total row (menu table: add total row), with all variants. If these totals are used, the query does not give the right results. You can even change the fist collumn "totals" to a number. So what is a good idea to deal with this problem?

[#2]

Hi Bart,

Totals is the property of the table, you can't check it directly in the query. As workaround could be:

In any cell check the totals for your table like

=Table1[[#Totals],[a]]

it returns something if Total row exists and error if not

In Name Manager add the name for this cell, i took "CheckTableTotals"

Using it in your query check if Total row exists or not

    //Check if Totals row exists in the Table and remove it then

    SourceTotals = Excel.CurrentWorkbook(){[Name="CheckTableTotals"]}[Content],
    TestForError = try SourceTotals{0}[Column1],
    RowsToRemove = if TestForError[HasError] then 0 else 1,
    RemoveTotals = Table.RemoveLastN(Source,RowsToRemove)

    //Continue without Totals

Here i removed Total row from resulting table if it exists