IF empty return result from another table

Hi Anonymous 

When you click on the Enter data icon in PBI and enter data, it creates the M code for you.  If you click on Transform data the Power Query editor opens.  Expand the query pane on the left side of the window and then right-click the query and then click Advanced Editor.  You should now be looking at M code.

The image you've supplied shows the table in the PQ editor, so from there open the Advanced Editor.  Either right click the query as already decribed or click on Advanced Editor on the Home tab.

Regarding the SQL query, how is this executed?  Presumably in Power Query and if so then it also has a PQ query and some M code to get the value for 2020? 

How many queries do you see in the PQ Editor (left hand side of the editor window)?

You can get that 2020 value into the table by referencing the result of the SQL query.

I've prepared a sample to show how this can work, but you will need to adapt it to your situation, as I don't have your code or knowledge of your environment.  If you can provide your PBI file I can try to figure it out.

This codes creates the table you showed in the image above.  There's a separate query called SQLDummy that represents your SQL query.  The output from this is fed into the table using an if statement and a custom column.

You can download this PBIX file which contains both of these queries.

DataTable Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFPSUbI0ABKGBgZKsTpgMXOQmBmqmAVIzAJVzBLINUfVawTiwkRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, TempResult = _t, KPI = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"TempResult", Int64.Type}, {"KPI", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each if [Year] = 2020 then SQLDummy else [TempResult]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"TempResult"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Year", "Result", "KPI"})
in
    #"Reordered Columns"

SQLDummy Query

let
    Source = 30
in
    Source

Regards

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.