Power Query - How to return ALL values when parameter is left blank

[tbablue]

Hi Forum,

I'm trying to implement a null check into this line of my code - so that my Func_SubTeam will work if the parameter is left empty - but I can't get it to run without error - can anyone help?

#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Func_SubTeam", each Func_SubTeam([Sub Team])),

Any help welcomed.

 

[alansidman]

You need an if statement that says if the value of the parameter is null then select the earlier line of MCode that happens before filtering for the parameter. If you show your entire Mcode, we might be able to modify it for you if you cannot figure out my previous statement.

 

[tbablue]

Really grateful for your attention..

This is my code

let
Source = Excel.CurrentWorkbook(){[Name="Para_SubTeam"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sub Team", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Func_SubTeam", each Func_SubTeam([Sub Team])),
#"Expanded Func_SubTeam" = Table.ExpandTableColumn(#"Invoked Custom Function", "Func_SubTeam", {"Period", "Yr.Mth (Period)"})
in
#"Expanded Func_SubTeam"



I've tried to implement each Func_SubTeam = null or - but it errors when I introduce it ...

#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Func_SubTeam", each Func_SubTeam = null or Func_SubTeam([Sub Team])),

 

[alansidman]

Here is an example of what I was describing earlier. This is involves a parameter query and the OP wanted to have it return all records when the parameter was null

let
    Bron = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    #"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"LF run", Int64.Type}, {"LF back", Int64.Type}, {"LF front", Int64.Type}, {"Draaiuren", Int64.Type}, {"Kolom2", type datetime}, {"kg/h", Int64.Type}, {"Quality", Int64.Type}}),
    #"Rijen gegroepeerd" = Table.Group(#"Type gewijzigd", {"LF run", "LF back", "LF front"}, {{"Aantal", each List.Sum([#"kg/h"]), type nullable number}, {"lab", each List.Average([Quality]), type nullable number}, {"draaruren", each List.Min([Draaiuren]), type nullable number}, {"daaiuren", each List.Max([Draaiuren]), type nullable number}}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Rijen gegroepeerd", "Aangepast", each [daaiuren]-[draaruren]),
    #"Type gewijzigd1" = Table.TransformColumnTypes(#"Aangepaste kolom toegevoegd",{{"LF back", type text}, {"LF front", type text}}),
    #"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Type gewijzigd1", "Aangepast.1", each [LF back]&" "&[LF front]),
    #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Aangepaste kolom toegevoegd1",{"LF run", "Aangepast.1", "LF back", "LF front", "Aantal", "lab", "draaruren", "daaiuren", "Aangepast"}),
    #"Filtered Rows" = Table.SelectRows(#"Volgorde van kolommen gewijzigd", each Text.Contains([Aangepast.1], Table2)),
    NullStep=if Table2 <> null then #"Filtered Rows" else #"Volgorde van kolommen gewijzigd"
in
    NullStep

Look at the line of code for NullStep to understand my previous post. Table2 contains the parameter.