[SOLVED] If condition then Query1 else Query2

Bernard Heymans
Posts: 16
Joined: Wed Feb 28, 2018 12:18 am

[SOLVED] If condition then Query1 else Query2

Postby Bernard Heymans » Fri Jun 07, 2019 10:06 pm

Hello the Forum,

My cube is all transaction for several years. The data is structured by department.
In the query I have a parameter that allow to select the department that should be loaded.

but now I start to have requests from managers when going together...

So should be able to load ALL departments. I want to keep my report (Excel) unique.
I have found a way to make an if then else... BUT I would love to have it at line level while it is now a switch between 2 complete queries.

As a fact, just switching one line off should be enough. It is the line selecting the SBU...
" #"Filtered Rows" = Table.SelectRows(#"Filtered to data", each Text.Contains([Name], fctnParameter("SBU"))), "

could be replaced by sopmething like this:

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

  #"Filtered Rows" = if fctnParameter("Data Name") ="All" 
                                        then Table.SelectRows(#"Filtered to data", each Text.Contains([Name], fctnParameter("SBU")))
                                        else #"Filtered to Data",

But I can't get it working...



Can someone help? Thanks!!

Bernard



{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

let
    Source = AllFiles,

   
// case "All"
    #"Filtered to dataBB" = Table.SelectRows(Source, each Text.Contains([Name], fctnParameter("Data Name"))),
    //#"Filtered Rows" = Table.SelectRows(#"Filtered to dataBB", each Text.Contains([Name], fctnParameter("SBU"))),
    #"Invoked Custom FunctionBB" = Table.AddColumn(#"Filtered to dataBB", "fctnOneyearMulti", each F_OneYear([TheFile])),
    #"Sorted RowsBB" = Table.Sort(#"Invoked Custom FunctionBB",{{"Name", Order.Ascending}}),
    #"Removed Other ColumnsBB" = Table.SelectColumns(#"Sorted RowsBB",{"fctnOneyearMulti"}),
    #"Expanded fctnOneyearMultiBB" = Table.ExpandTableColumn(#"Removed Other ColumnsBB", "fctnOneyearMulti", {"Cycle", "Date - Calendar Year/Month", "Planning Level", "Material", "Net Sales", "GM ", "Qtty"}, {"Cycle", "Date - Calendar Year/Month", "Planning Level", "Material", "Net Sales", "GM ", "Qtty"}),
    #"Changed TypeBB" = Table.TransformColumnTypes(#"Expanded fctnOneyearMultiBB",{{"Cycle", type text}, {"Planning Level", type text}, {"Material", type text}, {"Date - Calendar Year/Month", type date}, {"Net Sales", type number}, {"Qtty", type number}, {"GM ", type number}}),
    #"Case_All" = #"Changed TypeBB",



// case one SBU selected
    #"Filtered to data" = Table.SelectRows(Source, each Text.Contains([Name], fctnParameter("Data Name"))),
    #"Filtered Rows" = Table.SelectRows(#"Filtered to data", each Text.Contains([Name], fctnParameter("SBU"))),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "fctnOneyearMulti", each F_OneYear([TheFile])),
    #"Sorted Rows" = Table.Sort(#"Invoked Custom Function",{{"Name", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"fctnOneyearMulti"}),
    #"Expanded fctnOneyearMulti" = Table.ExpandTableColumn(#"Removed Other Columns", "fctnOneyearMulti", {"Cycle", "Date - Calendar Year/Month", "Planning Level", "Material", "Net Sales", "GM ", "Qtty"}, {"Cycle", "Date - Calendar Year/Month", "Planning Level", "Material", "Net Sales", "GM ", "Qtty"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded fctnOneyearMulti",{{"Cycle", type text}, {"Planning Level", type text}, {"Material", type text}, {"Date - Calendar Year/Month", type date}, {"Net Sales", type number}, {"Qtty", type number}, {"GM ", type number}}),
    #"Case_Other" = #"Changed Type",


result =
    if fctnParameter("SBU") = "All"
         then #"Case_All"
         else #"Case_Other"



in
    #"result"
Last edited by Bernard Heymans on Thu Jun 13, 2019 8:17 pm, edited 1 time in total.

PhilC
Posts: 261
Joined: Tue Sep 09, 2014 8:13 am

Re: If condition then Query1 else Query2

Postby PhilC » Tue Jun 11, 2019 2:05 pm

Hi Bernard,

You mentioned your suggestion does not work, providing some information around the error would be useful, does it error, does it not work functionally the way you want it to?

The business logic seems to be
IF Parameter called Data Name is "All", use Parameter SBU to filter the data, on a Text Contains basis, otherwise do no filtering.

Would the following work?

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

= if fctnParameter("Data Name") = "All" then Table.SelectRows(#"Filtered to data", each Text.Contains([Name], fctnParameter("SBU"))) else Table.SelectRows(#"Filtered to data", each true)


The difference to your suggested code is the else part of the if, it basically does no filtering.

If more help is needed, posting a file (with mocked up data if needed) would be useful.

Cheers
Phil

Bernard Heymans
Posts: 16
Joined: Wed Feb 28, 2018 12:18 am

Re: If condition then Query1 else Query2

Postby Bernard Heymans » Wed Jun 12, 2019 11:13 pm

Hi Phil,

Thanks for the suggestion.

Actually I found a way to go around the problem:
"Mon_Filtre" is a variable...
& if "All" is selected, I duplicate an other filter (so filter nothing out)
In the example below, I filter twice on "Invoked_Doc_Type".


{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

let
    Mon_Filtre = if Invoked_Question="All" then Invoked_Titre
                                             else Invoked_Doc_Type,

    Source = Folder.Files("C:\Users\EU6732\Downloads"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Extension", "Folder Path"}),

    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each Text.Contains([Name], Mon_Filtre))
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Name], Invoked_Doc_Type))
 in
    #"Filtered Rows2"


Regards,

Bernard


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 3 guests