topic Re: Adding index column based on another query, issues with data types in Dataflow

Adding index column based on another query, issues with data types

nioj2S2 — Wed, 13 Mar 2024 11:52:10 GMT

Hello!

I am trying to do a workaround for the missing IDENTITY column in my Fabric warehouse in order to have a surrogate key.

In the DW I have column [surrogateKey] in the empty accounts table. The new key should be based on the max existing key+1 and then incrementally add 1.

In the Dataflow gen2 I have a query MaxKey_Accounts to first get the max value from the surrogateKey column (or return 0 if no value exists):

let Source = Fabric.Warehouse([]), #"Navigation 1" = Source{[workspaceId = "yyy"]}[Data], #"Navigation 2" = #"Navigation 1"{[warehouseId = "xxx"]}[Data], #"Navigation 3" = #"Navigation 2"{[Schema = "zzz", Item = "accounts"]}[Data], #"Removed other columns" = Table.SelectColumns(#"Navigation 3", {"surrogateKey"}), #"Calculated maximum" = if List.Max(#"Removed other columns"[surrogateKey]) is null then 0 else List.Max(#"Removed other columns"[surrogateKey]) in #"Calculated maximum"

This saves as a decimal based on the "1.2" icon next to the name under "Queries" field to the left.

... then I want to use this result as a base for the identity in another query (code excerpt):

Table.AddIndexColumn(#"Changed column type 1", "surrogateKey", MaxKey_Accounts+1, 1)

Gives error: "We cannot apply operator + to types Table and Number."

I have tried casting the MaxKey_Accounts as well:

Table.AddIndexColumn(#"Changed column type 1", "surrogateKey", Number.From(MaxKey_Accounts)+1, 1)

Gives error "We couldn't convert to Number."

And this way too:

Table.AddIndexColumn(#"Changed column type 1", "surrogateKey", Table.Max(MaxKey_Accounts)+1, 1)

Gives error: "We cannot convert a value of type Table to type Text."

So, how can I take the max value from existing DW table and use that in creating an index column for new data into that table?

Thank you!

Re: Adding index column based on another query, issues with data types

Anonymous — Thu, 14 Mar 2024 05:51:30 GMT

Hi nioj2S2

Thanks for using Microsoft Fabric Community.

Apologies for the issue that you are facing.

This might require a deeper investigation from our engineering team to help you better. Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support
Please provide the ticket number here as we can keep an eye on it.

Thanks

Re: Adding index column based on another query, issues with data types

mandania — Thu, 14 Mar 2024 08:17:32 GMT

i am facing exactly the same issue!

Re: Adding index column based on another query, issues with data types

miguel — Fri, 15 Mar 2024 21:00:13 GMT

are any of your queries set to be staged? specifically the MaxKey_Accounts query

Re: Adding index column based on another query, issues with data types

Anonymous — Mon, 18 Mar 2024 07:00:01 GMT

Hi nioj2S2
We haven’t heard from you on the last response and was just checking back to see if you got a chance to create a support ticket. If yes please provide the details here. Otherwise, will respond back with the more details and we will try to help.
Thanks

Re: Adding index column based on another query, issues with data types

nioj2S2 — Mon, 18 Mar 2024 08:16:13 GMT

Hi Anonymous

Yes, I have been in call with support, awaiting their response after the information I provided them with.

Re: Adding index column based on another query, issues with data types

miguel — Mon, 18 Mar 2024 08:44:49 GMT

Hi nioj2S2, would you mind checking the previous reply that I provided?

This might not require the assistance of the support team.

Copying the previous reply that I left:

are any of your queries set to be staged? specifically the MaxKey_Accounts query

Re: Adding index column based on another query, issues with data types

nioj2S2 — Mon, 18 Mar 2024 10:21:02 GMT

Yes, they both were as default. Changed the MaxKey_Accounts query to not be, and it worked! Thanks a lot! 🙂

Note: it did however require some other troubleshooting as it did not work at first, but that was due to the DROP and CREATE table functions in the warehouse. When I first created the tables I used:

columnName varchar NULL,

... and for reasons I did a DROP and CREATE in which the automated code created did this:

columnName varchar(1) NULL,

... which caused other insert errors since all data was more than 1 varchar big (not related to the index column) but I'm leaving this info here for others so they don't make the same mistake I did.

Follow-up question: if staging the MaxKey_Accounts returns it as a table in the index column creation, how come Number.From did not work in converting it into a number?

Re: Adding index column based on another query, issues with data types

miguel — Mon, 18 Mar 2024 15:47:03 GMT

when you stage a query it needs to be saved as a table. If you had a scalar value, like a number, simply referring to that query would yield the scalar value (Number). However, if its a table (like it was in this case because the query was staged) then you'd need to do the table navigation to reach the value that you're looking for. It could be something like QueryName[ColumnName]{0} where the 0 indicates that you just want the first row.

Number.From works against scalar values and not complex ones (like records, tables, lists and such). More info on the signature of the function below:

Number.From - PowerQuery M | Microsoft Learn

Re: Adding index column based on another query, issues with data types

nioj2S2 — Tue, 19 Mar 2024 11:12:08 GMT

Thanks a lot! 😄