power query error "expression error"

[adlbrk]

Hi, I'm getting a recurring expression error on this 3326 tab, where nothing had been change prior in power query.

this is what it looks like within power query:

when I select "go to error" it sends me back to this step:

Please advise thanks

[#2]

Hi @adlbrk 

Realistically speaking I don't think someone can determine the source of the issue with pictures only

PQ usually makes no mistakes when it says The column 'xyz' of the table wasn't found ==> It's highly probable that [2024-01-02] is referenced in a step where that column doesn't exist

Could you post, at least, the complete 'city 3326' query code?

or better share your workbook as I'm under the impression that query depends on other(s)

[#3]

Hi@Lorenzo 

Here's the formula that appears to be in error:

= Table.TransformColumnTypes(#"Promoted Headers",{{"2024-01-02", type date}})

[#4]

Hi @adlbrk 

The step that raises the error doesn't help I'm afraid (I wrote Could you post, at least, the complete 'city 3326' query code?)

When you're in the Power Query Editor, click (in APPLIED STEPS) on the step Promoted Headers (highlighted in blue below):

and check the table Headers (column names) - Sure none of them is named 2024-01-02

[#5]

@Lorenzo 

promote headers formula is this:
= Table.PromoteHeaders(#"Renamed Columns", [PromoteAllScalars=true])

[#6]

@adlbrk 

Don't get me wrong but it's difficult to believe...

• In my 1st reply I asked you to post, at least, your complete query code
  => Still not received it
• In my 2d reply I wrote "When you're in the Power Query Editor, click (in APPLIED STEPS) on the step Promoted Headers...and check the table Headers (column names) - Sure none of them is named 2024-01-02
  => You post a pic. showing the Promoted Headers step but do not tell me if any of the column (at that step) is named 2024-01-02

If you want to sort this issue out - with help from forum(s) - please provide the information you're asked

That said, a couple of things are obvious to me:

1. Looking at the query step names, that query has been modified. No problem in principle as long as we understand how a query works and we know what we do
2. Looking at your last picture it's interesting to note that the 1st few rows of the table (at the Promoted Headers step) show 2024-01-02 in the [Date] column
3. There's a Renamed Columns step prior to the Promoted Headers step. I wonder what column(s) are renamed at that step???

[#7]

@Lorenzo 

apologies, I didn't know where the power query code was til now.

let
Source = Web.Page(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vRJ32aTLkpVH9REqz4sHP0xuvvMLRU4lGRZNFkRLhKzK36CkLsJo...")),
Data0 = Source{0}[Data],
#"Removed Columns" = Table.RemoveColumns(Data0,{"Column1", "Column2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column3", "Date"}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Renamed Columns", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"2024-01-02", type date}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type2",{{"2024-01-02", "Date"}, {"ACH Electronic Debit - AMERICAN EXPR ACH PMT A0178 1", "Description"}, {"Checking", "Type"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns3", "-890.16", Splitter.SplitTextByDelimiter("-", QuoteStyle.None), {"-890.16.1", "-890.16.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter1",{{"-890.16.2", "Debit"}, {"-890.16.1", "Credit"}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Renamed Columns1",1),
#"Sorted Rows" = Table.Sort(#"Removed Bottom Rows",{{"Date", Order.Ascending}}),
#"Inserted Week of Month" = Table.AddColumn(#"Sorted Rows", "week of year", each Date.WeekOfMonth([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Week of Month", "Month", each Date.MonthName([Date]), type text)
in
#"Inserted Month Name"

[#8]

@Lorenzo 

sorry it's taking me a while here. some of headers are renamed at that step but that shouldn't be an issue. Also, they weren't an issue until a couple weeks ago, and nothing was changed. see pics below:

[#9]

Hi @adlbrk 

Also, they weren't an issue until a couple weeks ago, and nothing was changed

I earlier told you the initial query was modified & you seem to say I was wrong - Fair enough. Assuming no change was made to the query, the only other possibility is: the query was built on a data source that isn't formatted the same as the one you currently use. Anyway, below is a summary of the issues:

The problem wasn't only re. column [2024-01-02]. There was a sub-sequent step Splitting column [-890.16] that doesn't exist either. And the next one Renaming the splitted columns as [Debit] & [Credit] that logically failed as well

With your pictures and the query code I understood the sequence and what you seem to want to do. Without having access to the data source (a Google Sheets doc.) I can't be 100% sure but simulated things with a CSV. So, replace your complete query code with the following (don't forget to update the path on the Web.Contents line):

let
    Source = Web.Page(
        Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vRJ32aTLkpVH9REqz4sHP0xuvvMLRU4lGRZNFkRLhKzK36CkLsJo...")
    ),
    Data0 = Source{0}[Data],
    #"Removed Columns" = Table.RemoveColumns(Data0, {"Column1", "Column2"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Date", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",
        { {"Name", "Description"}, {"Account", "Type"} }
    ),
    #"Added Debit" = Table.AddColumn(#"Renamed Columns", "Debit", each
        let Amt = Number.From([Amount])
        in try (if Amt < 0 then Amt else null) otherwise null,
        type number
    ),
    #"Added Credit" = Table.AddColumn(#"Added Debit", "Credit", each
        let Amt = Number.From([Amount])
        in try (if Amt >= 0 then Amt else null) otherwise null,
        type number
    ),
    #"Removed Amount" = Table.RemoveColumns(#"Added Credit", {"Amount"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Amount", 1),
    #"Sorted Rows" = Table.Sort(#"Removed Bottom Rows",{{"Date", Order.Ascending}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Sorted Rows", "week of year", each Date.WeekOfMonth([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Week of Month", "Month", each Date.MonthName([Date]), type text)
in
    #"Inserted Month Name"

Side notes:

• Your #"Removed Bottom Rows" step should be just after the #"Removed Columns" step below Data0 - For efficiency reason
• The formula @ step #"Inserted Week of Month" does what is says but you name the column [week of year] - Quite confusing, isn't it?

[#10]

Hi @adlbrk 

How are things going with the proposed query? In the event where this fixed the issue it would be appreciated you click the Mark as response link at the bottom of the post to help people who Search - Thanks