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

Question

Hi all,&nbsp;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.&nbsp;

amitchandak · Answer

Anonymous , Try like
if [Days taken to complete] = null then 0 else [Days taken to complete]

anonymous · Answer

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

philiptreacy · Answer

Hi&nbsp;Anonymous&nbsp;You can check if the table is empty then insert a row, something like this example.&nbsp;In the last step #Added Custom#&nbsp;the code checks for an empty table then uses Table.InsertRows&nbsp;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] &lt;&gt; 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"&nbsp;NOTE:&nbsp;I've used a dummy table consisting of the columns I could see in you screen shot.&nbsp; You'll need to adjust the Table.InsertRows function to include all the columns in your real tablePhilIf 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 · Answer

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

philiptreacy · Answer

Hi&nbsp;Anonymous&nbsp;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.&nbsp; You don't change the Table.FromRows in my code, it will break the code.ThanksPhilIf 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.

Forum Discussion

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

9 Replies

Related content

Power Query - Table in Column

How to filter a Table column in Power Query?

Power Query - Add column from related table

Need column to return date in working days from today, from query table column.

Table Query