In Excel Power Query, can I return a specific table if the data is empty, but return the data if it exists?

Question

I'm using Excel to create a fire weather product for a specific National Forest. One of the tables reported includes National Fire Danger Rating System (NFDRS) indices and weather observations from Remote Automated Weather Stations (RAWS). This information is pulled in CSV format from the Fire Environment Mapping System (FEMS). This system can provide hourly observations, and I need to pull the 1400 (2:00 PM) observations for the afternoon/evening forecast. I currently have the query set up so that it pulls from roughly midnight to midnight local time (the times in the URL in the code below are UTC), just in case they decide they want averages, minimums, and maximums instead of a snapshot down the road.

So, if it's before the 1400 observations have posted, in step 6 ("Filtered Rows"), it has an empty table, because anything that isn't the 1400 time is filtered out.

What I'm looking for is a way to fork at this point, so that if the table is empty it creates a blank table with the appropriate headers and first column, and returns the appropriate table if the data is present. I feel like this can be done with a conditional statement and using Table.IsEmpty, but I can't wrap my brain around how to make it work.

Any assistance is appreciated.

let
    Source = Csv.Document(Web.Contents("https://fems.fs2c.usda.gov/api/ext-climatology/download-nfdr?stationIds=361002,361231&endDate="&GetValue("Tomorrow")&"T04:59:59Z&startDate="&GetValue("Today")&"T04:00:00Z&dataFormat=csv&dataset=observation&fuelModels=Y&dateTimeFormat=UTC"),[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"stationName", "Station"}, {"observationTime", "Time"}, {"oneHR_TL_FuelMoisture", "1 Hr"}, {"tenHR_TL_FuelMoisture", "10 Hr"}, {"hundredHR_TL_FuelMoisture", "100 Hr"}, {"thousandHR_TL_FuelMoisture", "1000 Hr"}, {"kbdi", "KBDI"}, {"woodyLFI_fuelMoisture", "Woody FM"}, {"herbaceousLFI_fuelMoisture", "Herb FM"}, {"ignitionComponent", "IC"}, {"energyReleaseComponent", "ERC"}, {"spreadComponent", "SC"}, {"burningIndex", "BI"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Time", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Date", "Time"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"1 Hr", type number}, {"10 Hr", type number}, {"100 Hr", type number}, {"1000 Hr", type number}, {"KBDI", type number}, {"Woody FM", type number}, {"Herb FM", type number}, {"IC", type number}, {"ERC", type number}, {"SC", type number}, {"BI", type number}, {"Date", type date}, {"Time", type time}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInCurrentDay([Date]) and [Time] = #time(14, 0, 0)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Station", "Date", "IC", "SC", "ERC", "BI", "KBDI", "1 Hr", "10 Hr", "100 Hr", "1000 Hr", "Woody FM", "Herb FM"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Time","NFDRType", "fuelModelType", "gsi", "NFDRQAFlag"}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Removed Columns"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers1",{{"ALLEGHENY", "Allegheny"},{"KINZUA","Kinzua"}}),
    #"Added Average Column" = Table.AddColumn(#"Renamed Columns1", "Averages", each List.Average({[Allegheny],[Kinzua]}))
in
    #"Added Average Column"

Note 1: GetValue() is a custom formula that works with a named cell that contains a date formatted for the URL.

Note 2: While I mostly understand the code above, I am very new to Power Query and M, and I'm learning as I go. I'm sure there is a more efficient way of doing all this, but I don't know what it is.

kb2136 · Accepted Answer · 2025-10-30 15:04:29Z

1

Instead of forking, simply replace all the values with null, if they describe observations taken outside of the 1400 point.
To do this in multiple columns at once, you can take advantage of List.Accumulate.
Substitute the #"Filtered Rows" step with this one:


null_if_not_14 = List.Accumulate(
        {"Column1", "Column2", "Column3"}, // list of column names, where you wish to show nulls instead of the actual values (modify to meet your requirements)
        #"Changed Type", // the previous step
        (tbl, next_col) => 
            Table.ReplaceValue(
                tbl,
                each Record.Field(_, next_col),
                each if [Time] = #time(14, 0, 0) then Record.Field(_, next_col) else null,
                Replacer.ReplaceValue,
                {next_col}
            )              
        )

answered Oct 30 at 15:04

kb2136

1478 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Giric Red Wolf Oct 30 at 15:19

I had an epiphany and came up with a different solution using an if statement, Table.IsBlank, and Table.InsertRows. I'll post it in a moment and answer my own question. Essentially, as long as I have two rows with my Station names in them and the rest of the fields null, I should be good. I appreciate the quick response, though! I will play with what you have there to see what it does and how it works. It might be more elegant than my solution!

Giric Red Wolf · Accepted Answer · 2025-10-30 15:28:32Z

I did some more searching around and trying to figure out how to get a conditional statement into the Power Query M code. I did not know that #"Table Name" = if <condition> then <operation> else <other operation> was doable, so here's what I have now, snipped in.


    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInCurrentDay([Date]) and [Time] = #time(14, 0, 0)),
    #"Empty Check" = if Table.IsEmpty(#"Filtered Rows") then Table.InsertRows(#"Filtered Rows", 0, {[Station = "ALLEGHENY", Date = null, Time = null, NFDRType = null, fuelModelType = null, 1 Hr = null, 10 Hr = null, 100 Hr = null, 1000 Hr = null, KBDI = null, gsi = null, Woody FM = null, Herb FM = null, IC = null, ERC = null, SC = null, BI = null, NFDRQAFlag = null], [Station = "KINZUA", Date = null, Time = null, NFDRType = null, fuelModelType = null, 1 Hr = null, 10 Hr = null, 100 Hr = null, 1000 Hr = null, KBDI = null, gsi = null, Woody FM = null, Herb FM = null, IC = null, ERC = null, SC = null, BI = null, NFDRQAFlag = null]}) else #"Filtered Rows",
    #"Reordered Columns" = Table.ReorderColumns(#"Empty Check",{"Station", "Date", "IC", "SC", "ERC", "BI", "KBDI", "1 Hr", "10 Hr", "100 Hr", "1000 Hr", "Woody FM", "Herb FM"}),

To explain, eventually we get to where we filter out everything but the rows where the [Date] column has the current date and the [Time] column is 1400 (2:00 PM), and this is where #"Filtered Rows" picks up.

Then, we check to see if #"Filtered Rows" is empty. If it is, insert rows with a station name and the rest of the fields as null. If it isn't, keep #"Filtered Rows" and move on.

My original premise above was based in a lack of knowledge - not know that = if was possible and thinking that the language required an immediate function.

If I were you, I would create a blank query, do not enable the query to load. Make the table you just made here, then your function looks much more readable. if Table.IsEmpty(#"Filtered Rows") then NullTableInsert else #"Filtered Rows"

Collectives™ on Stack Overflow

In Excel Power Query, can I return a specific table if the data is empty, but return the data if it exists?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related