Power M generated GUID, how to use in subsequent steps

[evanmandos]

Hi All,

Currently i am in the process of transitioning from a novice to an experienced fabric user hence i have more and more use for M/Power Query from the Gen2 dataflows aspect.

Currently i am working on a routine where i need to load some data from a source into a sink system. In the sink system i do have a need to insert records with a generated unique UUID.

After scouring this forum i have found the right workaround to actually generate unique ID's in my data but i am experiencing issues if i want to use those values in further activities in my flow

Consider 2 activities, 'add products' and 'add productitems'

In activity 'add products' i use the following line to generate the unique GUID's:

  #"Added Guid" = Table.TransformColumnTypes(Table.AddColumn(#"Duplicated column", "id2", each try error Text.NewGuid() otherwise Text.NewGuid()), {{"id2", type text}}),

This activity then writes it dataset to the destination(product table)

The next step 'add productitems' is started by referencing or duplicating the 'add products' dataset with the aim to transform the dataset and write the transformed set to another destination(productitems table) with following code:

let

  Source = #"add products",

  #"next" = [do some tranformation and merging magic steps to achieve the correct dataset for productitems]

in

  #"next"

My issue is that the ID's (column "id2" in the example) generated in 'add product' are not keeping their values after the reference/duplication of the dataset, it seems that M is re-evaluating the id2 column values on referencing/duplication of the dataset

Does anyone have the silver bullet in this issue to prevent M re-evaluating the GUID values? Or is there any long way round to convince M to value-reference these generated values throughout the entire dataflow?

[evanmandos]

@lbendlin @v-pnaroju-msft Many thanks for the response, you very helpfully clarify to me what the functional limitation is, i have indeed approached it a bit different now handling the generated ID's separately and using extra db-roundtrips and the case is operational.

[v-pnaroju-msft · Accepted solution]

Hi evanmandos,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Thankyou, @lbendlin, for your response.

Hi @evanmandos,

We sincerely appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

Based on my understanding, this behaviour arises from the way Power Query operates. Power Query is a functional, stateless language, and expressions such as Text.NewGuid() are re-evaluated every time they are invoked, unless the result is materialised.

Kindly follow the steps mentioned below to persist your generated GUIDs across flow steps like 'add products' and 'add productitems', which may help resolve the issue:

1. In your 'add products' step, generate the GUID using the following code:
   Table.AddColumn(#"Previous Step", "id2", each Text.NewGuid(), type text)

2. Write this table to a persistent sink such as Azure Data Lake, Fabric Lakehouse, or any SQL destination.

3. In the 'add productitems' step, read from that sink instead of referencing the 'add products' step. This will ensure that the GUIDs are fixed and do not change with each query execution.

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[lbendlin]

Power Query has no memory. It can and will run your query more than once.  You need to create your unique identifier further upstream if you want it to persist.