Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

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...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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