Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a table that I want to output 4 columns sometimes and 5 columns other times. I want to write something that will conditionally skip a merge statement in the advanced editor. Is this possible? Alternatively, I'm looking for a way to avoid having my power queries refresh under these same conditions. I am aware of the query property that allows you to Turn Off Refresh All option; however, I want the end users to be able to hit Refresh All and have the data refresh when appropriate, so this is not an ideal solution for me. This is in Excel
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", 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}}),
choice1 =
let
next1 = Table.AddColumn(ChangedType, "Column2", each [Column1] + 1),
final1 = Table.AddIndexColumn(next1, "Index" )
in
final1,
choice2 =
let
next2 = Table.AddColumn(ChangedType, "Column2", each [Column1] + 5),
final2 = Table.NestedJoin(next2, "Column1", next2, "Column1", "NestedJoin" )
in
final2,
conditional_stmt = if Time.Second(DateTime.LocalNow()) > 30 then choice1 else choice2
in
conditional_stmt
You can do an if condition then Table.NestedJoin .... else then PriorStep
Or you can do a
try Table.NestedJoin(.....) otherwise PriorStep
if you want it to try to merge and if it fails then do something else.
This worked for me in the Advanced Editor:
I am using it to reduce the dataset while I edit the Formulas. It makes the tables smaller so that the formulas load faster. I added it right after the source on each of my tables. It references a manually entered table that I edit the amount of rows I would like to show. If I set that table to 0 it shows all the rows in the table. Pretty neat.
ReduceDatasetFIlter = if Table.FirstValue(ReferenceTable) = 0 then Source else Table.LastN(Source, Table.FirstValue(ReferenceTable)),
I believe I tried that .. could you provide an example or the syntax? I don't think you can just write a contitional like that straight in the advanced editor. It's not just one step that I'm trying to skip. I'm trying to skip 5-6 steps that transform that column after it is merged too.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", 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}}),
choice1 =
let
next1 = Table.AddColumn(ChangedType, "Column2", each [Column1] + 1),
final1 = Table.AddIndexColumn(next1, "Index" )
in
final1,
choice2 =
let
next2 = Table.AddColumn(ChangedType, "Column2", each [Column1] + 5),
final2 = Table.NestedJoin(next2, "Column1", next2, "Column1", "NestedJoin" )
in
final2,
conditional_stmt = if Time.Second(DateTime.LocalNow()) > 30 then choice1 else choice2
in
conditional_stmt
This worked brilliantly. Thank you!
on second thought, you might want to put the steps directly in the if then statement because they will both run and reduce execution speed because you are running unncessary steps. If you put them only in the if statement the second part will only run when the first part fails.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", 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}}),
conditional_stmt =
if
Time.Second(DateTime.LocalNow()) > 30
then
let
next1 = Table.AddColumn(ChangedType, "Column2", each [Column1] + 1),
final1 = Table.AddIndexColumn(next1, "Index" )
in
final1
else
let
next2 = Table.AddColumn(ChangedType, "Column2", each [Column1] + 5),
final2 = Table.NestedJoin(next2, "Column1", next2, "Column1", "NestedJoin" )
in
final2
in
conditional_stmt
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.