Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have several queries that contains several steps each.
In a query B, can I refer to the result of a step called STEP NUMBER 4 in another query called let's say A ?
If yes, how can I access it ?
And is it mandatory to have query A that is activated for loading and/or included in the report update ?
Hi @Anonymous ,
short answer: Never use #shared in production. It won't refresh in the service. It is an interesting function to play with, but nothing to use for production purposes.
I like the goodly-solution very much.
There is also a variation to it here in the comments on LinkedIn which allows you to keep the code of your existing subsequent queries: https://www.linkedin.com/feed/update/urn:li:activity:7029530911066968066?commentUrn=urn%3Ali%3Acomment%3A%28activity%3A7029530911066968066%2C7029567762314223616%29
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Anonymous ,
yes, you are basically overwriting the existing metadata.
Instead you can combine it into one record like so:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Intermediate Step1" = Table.TransformColumns(#"Changed Type", {{"Column1", each _ * 2, type number}}),
#"Intermediate Step2" = Table.TransformColumns(#"Intermediate Step1", {{"Column1", each _ * 3, type number}}),
#"Final Step" = Table.TransformColumns(#"Intermediate Step2", {{"Column1", each _ * 4, type number}}),
#"Add Metadata with Intermediate Steps" = #"Final Step" meta [ReferenceStepIntermediate1 = #"Intermediate Step1", ReferenceStepIntermediate2 = #"Intermediate Step2"],
in
#"Add Metadata with Intermediate Steps"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Anonymous ,
you could do it like described here:
Reference an intermediate step from a different query in Power Query (thebiccountant.com)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you for the solution !
I have tried the example shown in the website and wanted to check if we could store more than one intermediate step per query but it gives me an error when trying to access :
= Value.Metadata(Staging)[ReferenceStepIntermediate1]
Is it me doing it wrong or it is not possible, or doesn it need to be done in a different way ?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Intermediate Step1" = Table.TransformColumns(#"Changed Type", {{"Column1", each _ * 2, type number}}),
#"Intermediate Step2" = Table.TransformColumns(#"Intermediate Step1", {{"Column1", each _ * 3, type number}}),
#"Final Step" = Table.TransformColumns(#"Intermediate Step2", {{"Column1", each _ * 4, type number}}),
#"Add Metadata with Intermediate Step1" = #"Final Step" meta [ReferenceStepIntermediate1 = #"Intermediate Step1"],
#"Add Metadata with Intermediate Step2" = #"Final Step" meta [ReferenceStepIntermediate2 = #"Intermediate Step2"]
in
#"Add Metadata with Intermediate Step2"
It seems like another solution would be that one : https://goodly.co.in/extract-any-step-power-query/
From what I see it should allow us to refer to as many steps from the query as we want.
What do you think @ImkeF of the impact of this solution though (removing the "let .. in") ?
@ImkeF I've tried my luck asking chatgpt how to do it and it came up with an interesting solution :
SourceOfQueryB = #shared(QueryA)[StepNumber4]
It is not working yet on my PC but maybe it is due to a version of my Power BI Desktop, I have just downloaded version "2.112.1161.0 64-bit (décembre 2022)" though.
Here is the code of my 3 queries :
- 1st query Staging2Init :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}})
in
ChangedType
- 2nd query Staging2 :
let
Source = Staging2Init,
IntermediateStep1 = Table.TransformColumns(Source, {{"Column1", each _ * 2, type number}}),
IntermediateStep2 = Table.TransformColumns(IntermediateStep1, {{"Column1", each _ * 3, type number}}),
FinalStep = Table.TransformColumns(IntermediateStep2, {{"Column1", each _ * 4, type number}})
in
FinalStep
- 3rd query Loading2 :
let
Source = #shared(Staging2),
Personnalisé1 = Source[IntermediateStep1]
in
Personnalisé1
I get an error on line "Source = #shared(Staging2)," :
Formula.Firewall: Query 'Loading2' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
I thought the fact that I divided my original query into Staging2Init and Staging2 would prevent this error but it doesn't.
When I further asked chatgpt how to solve that, it told me that IntermediateStep1 should be set as a shared table, pointing me to a menu Table on the toolbar, then a menu Shared Tables, then Create a shared table, but I can't find it.
Does it ring a bell to use its suggested solution with the #shared keyword ? And if yes, how can I solve my error message here please ?
Thanks in advance
@ImkeF this is super cool !!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.