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

Hey!

I'm not sure I follow what you're trying to do with your function, since it's accesing the same file that you're trying to open from the AllSales2 query. Why not modify your function to transform a table as its input parameter? Basically just the last 2 steps of your query.

The error "They key didn't match any rows in the table" means that there's a step thats trying to access a data that it simply can't connect to for whatever reason it might be. One example could be that the data might not exists, or something's preventing to connection to it.

Answer 3

Also, could you share your workbook so we can repro the issue?

Answer 4

Hi @Miguel Escobar Here is the original Workbook :

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

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

Best regards,
Mourad

Answer 5

@Miguel Escobar , in fact I solved this problem with others methods but for educative purpose I have to solve this problem by using a function, so I would convert a query of one sheet, which I made a lot of transformations in it, so by converting this to a function will apply this to others sheets which have the same structure.
this method is very helpful if we work with many sheets which have the same structures for example 50 sheets and more.

Best regards,
Mourad