Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Filter table on a value that doesn't exist yet

Hi everyone

 

I'm quite new to Power BI. I'm trying to create a report that takes a sharepoint list that, for every month has a bit of data filled in. I want to transpose (?) that data into different columns for all the months. 

 

I did this my creating different queries for every month and filtering on the month column using this formula (for example, for october):

 

= Table.SelectRows(#"Expanded TrackID", each ([TrackID.Month] = 10))

 

 

Which worked perfectly up until this week. Now however, it gives an error in cases where there aren't any rows for that month.

 

The behavior that I want is, for example, if now rows have been found for january, I want power query to return an empty table.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Please try this way:

    let
    Source = #"Expanded TrackID",
    FilteredRows = Table.SelectRows(Source, each([TrackID.Month] = 1)),
    Result = if Table.IsEmpty(FilteredRows) then Table.FromRows({}, Table.ColumnNames(Source)) else FilteredRows
in
    Result


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Please try this way:

    let
    Source = #"Expanded TrackID",
    FilteredRows = Table.SelectRows(Source, each([TrackID.Month] = 1)),
    Result = if Table.IsEmpty(FilteredRows) then Table.FromRows({}, Table.ColumnNames(Source)) else FilteredRows
in
    Result


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.