Excel Power Query Custom Function Refresh Expression.Error

Question

NB. Below screens shot about source data, code and errors

Hello everyone,
I struggle with an example for educative purpose, so, I think normally it's a sample problem to solve with Power Query:
I have 3 sheets with sales for 3 months April and May and June 2016, all of those sheets have the same structure, so I want to append all those data in a main table, I followed those steps :

1) I imported the first step of "April 2016", so I cleaned up this sheet
2) I convert this query to a function to apply it to all sheets of this workbook
3) I import again the same workbook in the Power Query Editor
4) I choose only the source step of one sheet
5) I add a custom column, I apply may function to all sheets by name of each sheet
6) I delete all others columns then the custom column, and I extend all tables of this column and I change columns types
7) I save & load this query in my Excel desktop

All work fine but , When I add a row of a sheet and I refresh all I get an Expression.Error The Account Column not exist or the key didn't match.

I have the same Error when I remove a Column in the function in other case.

Why this problem occur in Power Query, Why this illogical Error.
What's the solution for this problem?

NB. Below screenshots about source data, code and errors
the link to the Workbook start :

https://drive.google.com/file/d/1hVuAJDLFlMjbFJG84TRuwQA8E293oowN/view?usp=sharing

Workbook with my solution : https://drive.google.com/file/d/1b5sP0OmU8UQT7vndE8cz2nBWdpUMBa2h/view?usp=sharing

Answer 1

A slightly different approach

Query:

let
    Source = Excel.Workbook(File.Contents("D:\Lorenzo\Downloads\Looukup sales V8.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each
        ([Attributes]?[Hidden]? <> true) and ([Kind] = "Sheet" and Text.EndsWith([Item]," 2016"))
    ),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns1", "fnCleansing", each fnCleansing([Data]), type table),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Data"}),
    #"Expanded fnCleansing" = Table.ExpandTableColumn(#"Removed Columns", "fnCleansing", {"Account", "Customer", "Department", "Amount"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded fnCleansing",{"Account", "Customer", "Department", "Amount"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",
        {{"Account", Int64.Type}, {"Customer", type text}, {"Department", Int64.Type}, {"Amount", Currency.Type}}
    )
in
    #"Changed Type"

fnCleansing:

(tbl as table) as table =>
let
    Source = tbl,
    #"Removed Top Rows" = Table.Skip(Source, 2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Answer 2

It is a good practice to create functions that can be reused across your scenarios.

I'd suggest that you try changing your queries for the following ones:

Here's the one for AllSales2:

let
    Source = Excel.Workbook(File.Contents("C:\Users\MiguelEscobar\Downloads\Looukup sales V8.xlsx"), null, true),
     #"Filtered Rows1" = Table.SelectRows(Source, each [Name] <> "AllSales2" and [Name] <> "AllSales21" and [Name] <> "ExternalData_1"),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each ([Name] <> "Index" and [Name] <> "Sheet1")),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "fnCleansing", each fnCleansing([Data])),
    #"Expanded fnCleansing" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnCleansing", {"Account", "Customer", "Department", "Amount"}, {"Account", "Customer", "Department", "Amount"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded fnCleansing",{"Account", "Customer", "Department", "Amount"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Account", Int64.Type}, {"Customer", type text}, {"Department", Int64.Type}, {"Amount", Currency.Type}})
in
    #"Changed Type"

The function should be this one:

(x)=>
let
    #"Removed Top Rows" = Table.Skip(x,2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Let me know if this answers your question.

Answer 3

thank you @Miguel Escobar and @Lz._
I followed the approach of Lorenzo, so I updated my code slightly to filter Item to select only Item that ends with "2016" to prevent others sheet and table which will be added by Power Query and Excel for this case.
So my current code work very well.

Query:

let  
    Source = Excel.Workbook(File.Contents("C:\Users\be_mo\OneDrive\Documents\Trainer\Project Trainer\Looukup sales Start.xlsx"), null, true),  
    #"Filtered Rows" = Table.SelectRows(Source, each Text.EndsWith([Item], "2016")),  
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "fnCleaning", each fnCleaning([Name])),  
    #"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"fnCleaning"}),  
    #"Expanded fnCleaning" = Table.ExpandTableColumn(#"Removed Other Columns", "fnCleaning", {"Account", "Customer", "Department", "Amount"}, {"Account", "Customer", "Department", "Amount"}),  
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded fnCleaning",{{"Account", Int64.Type}, {"Customer", type text}, {"Department", Int64.Type}, {"Amount", Currency.Type}})  
in  
    #"Changed Type"  

Function :

(X)=>  
let  
    Source = Excel.Workbook(File.Contents("C:\Users\be_mo\OneDrive\Documents\Trainer\Project Trainer\Looukup sales Start.xlsx"), null, true),  
    #"April 2016_Sheet" = Source{[Item=X,Kind="Sheet"]}[Data],  
    #"Removed Top Rows" = Table.Skip(#"April 2016_Sheet",2),  
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])  
in  
    #"Promoted Headers"