"Cannot convert value to type table" - when replacing table name with variable

Power Query
1

Hi all,

I’m trying to append a table based on a variable, original line of code that works:

= Table.Combine({RemoveColumns, #"202207ASP"})

Code with a variable, that returns an error

= Table.Combine({RemoveColumns, Number.ToText(Last_Act_Mth_FinYYYYM) & "ASP" } )

error is:

Expression.Error: We cannot convert the value “202207ASP” to type Table.
Details:
Value=202207ASP
Type=[Type]

The variable Last_Act_Mth_FinYYYYM is equal to 202207

I understand why this isn’t working because the value 2022ASP isn’t seen as a table, but not sure how to go about fixing it.

Thanks,
Tim

2

Hi @Timmay,

Yes, so you need to transform that value into a table, to be able to combine it with another table… Now you might still run into errors after that, so I’ll add a link to a chapter for you to review below.

#table(1, {{ Number.ToText( Last_Act_Mth_FinYYYYM ) & "ASP" }})

Would return:

image

Review this chapter if you want to learn more.

I hope this is helpful

1 Like
3

202207ASP is already a table, which is why I’m using

Table.Combine

The issue is PQ doesn’t see it as a table because I’m using a variable (Last_Act_Mth_FinYYYYM) instead of the string 202207ASP.

I want to use a variable because this value will change every month.

4

Hi @Timmay,

I still don’t get this remark because to the best of my knowledge you can’t dynamically change a variable or query name… as I understand it now you seem to be building a string to refer to a variable…

Nonetheless, I’m going to assume that is what you are referring to AND it is a separate query (IMPORTANT so not a step- or variable name in the same query!!). In such a case you can use the intrinsic #shared to get all functions, queries and so on within the Power Query instance.

let
    Source = #shared,
    ToTable = Record.ToTable(Source),
    GetTable = Table.SelectRows(ToTable, each [Name] = Number.ToText(Last_Act_Mth_FinYYYYM) & "ASP"){0}[Value],
    CombineTables = Table.Combine( { #"202207ASP", GetTable } )
in
    CombineTables

Here’s a working sample
eDNA - Convert value to table type.pbix (12.0 KB)

I hope this is helpful.

5

Thank you @Melissa for the solution.

Hello @Timmay, just following up if the response above help you solve your inquiry?

6

Hi @Timmay we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.