How to write a code in Power Query that fills an empty query/table with a zero in a specific column?

[Anonymous]

Hi all,

Is it possible to write a code that checks if the query in Power Query is empty or not and then fills in the 'Days taken to complete' as 0 if it is? For example, in the query below, there is no data for this month, however 2 other queries rely on there being data in this query in order to complete their operation, so I want to inout something that allows the system to check if the table if empty and basically input zero in here so that the other queries can complete the operation I've set it to do.

[v-easonf-msft]

Hi, @Anonymous 

If you still have no idea about your problem, you can open a support ticket with MSFT to seek technical support , usually they will have better suggestions.

https://powerbi.microsoft.com/en-us/support/

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi @PhilipTreacy ,

I'm having a few problems now with the code. 

Image 1: there is a quick win in the table, and so it shouldn't try and load a new table

Image 2: because the code says 'else null', is just replaces the table above with null

I've tried changing null to "" but it just leaves the table blank that way, any suggestions?

Charlotte

[Anonymous]

Here are the list of the Queries, I am trying to use this code for Quick WIn Average Time Taken, which originally gets all the information from Main Programme CI log, and then I've added manipulated this data to create this query. I am looking to insert it after step FilteredRows1 as two steps after (Avergae) is where the error loads from:

Here are all the table names in order:

[PhilipTreacy]

Hi @Anonymous 

OK the single line you need to insert into your code is 

#"Added Custom1" = if Table.IsEmpty(#"Filtered Rows1") then Table.InsertRows(#"Filtered Rows1", 0, {[Initiative Status = null, Target Date = null, Date Raised = null, Scale = null, Date Completed = null, Date Started = null, Project Controls = null, Days taken to complete = 0, Average = null]}) else null

You need to open the Advanced Editor and replace the #"Added Custom1" step shown in your screenshot above.

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.

[PhilipTreacy]

Hi @Anonymous 

The code I posted is created from a sample of columns I manually entered into PBI. If you can please post a sample of your data with the table names in order, I can rewrite the code for you.  You don't change the Table.FromRows in my code, it will break the code.

Thanks

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.

[PhilipTreacy]

Hi @Anonymous 

You can check if the table is empty then insert a row, something like this example. 

In the last step #Added Custom# the code checks for an empty table then uses Table.InsertRows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lEy1DfUNzIwMoAwjYBsQ0sgOzERSBgZgwgjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Scale = _t, #"Date Completed" = _t, #"Date Started" = _t, #"Project Controls" = _t, #"Days taken to complete" = _t, Average = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Scale", Int64.Type}, {"Date Completed", type date}, {"Date Started", type date}, {"Project Controls", type text}, {"Days taken to complete", Int64.Type}, {"Average", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Scale] <> 11)),
    #"Added Custom" = if Table.IsEmpty(#"Filtered Rows") then Table.InsertRows(#"Filtered Rows", 0,{ [Scale = null, Date Completed = null, Date Started = null, Project Controls = null, Days taken to complete = 0, Average = null]}) else null
in
    #"Added Custom"

NOTE: I've used a dummy table consisting of the columns I could see in you screen shot.  You'll need to adjust the Table.InsertRows function to include all the columns in your real table

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.

[Anonymous]

Hi Phil, thanks for this, if my other columns are 'Initiative Status', 'Target Date', 'Date Raised'. The step before where I've inserted this, is called #"Filtered Rows1", do I replace #"Added Custom" (at the end) with this. I also am not too sure what I change the first line to (after Table.From.Rows), do I need to update this with the name of the document and source? The only other 2 things are that I had to remove the hashtags so that it could reocgnise the columns in ["Scale" = _t, "Initiative Status" = _t,"Target Date" = _t, "Date Raised" = _t, "Date Completed" = _t, "Date Started" = _t, "Project Controls" = _t, "Days taken to complete" = _t, "Average" = _t]), Is this correct or should it be like above: 

And the last thing is all my columns are Decimal Number type's even the Date columns so I just changed all of them to {"Initiative Status", Int64.Type}, is this correct?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lEy1DfUNzIwMoAwjYBsQ0sgOzERSBgZgwgjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table ["Scale" = _t, "Initiative Status" = _t,"Target Date" = _t, "Date Raised" = _t, "Date Completed" = _t, "Date Started" = _t, "Project Controls" = _t, "Days taken to complete" = _t, "Average" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Initiative Status", Int64.Type}, {"Target Date", Int64.Type}, {"Date Raised", Int64.Type}, {"Scale", Int64.Type}, {"Date Completed", Int64.Type}, {"Date Started", Int64.Type}, {"Project Controls", Int64.Type}, {"Days taken to complete", Int64.Type}, {"Average", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Scale] <> 11)),
#"Added Custom" = if Table.IsEmpty(#"Filtered Rows") then Table.InsertRows(#"Filtered Rows", 0,{ [Initiative Status = null, Date Raised = null, Target Date = null, Scale = null, Date Completed = null, Date Started = null, Project Controls = null, Days taken to complete = 0, Average = null]}) else null
in
#"Added Custom"

Kind regards 

Charlotte

[amitchandak]

@Anonymous , Try like

if [Days taken to complete] = null then 0 else [Days taken to complete]

[Anonymous]

Hi, I think the problem is that there is nothing there, not even 'null', so unsure if it would be able to detect anything?