[Solved again] If condition then Query1 else Query2

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

[Solved again] 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:
[code] #"Filtered Rows" = if fctnParameter("Data Name") ="All"
then Table.SelectRows(#"Filtered to data", each Text.Contains([Name], fctnParameter("SBU")))
else #"Filtered to Data",
[/code]
But I can't get it working...



Can someone help? Thanks!!

Bernard



[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"[/code]
Last edited by Bernard Heymans on Thu Nov 07, 2019 10:40 pm, edited 4 times in total.

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

Re: If condition then Query1 else Query2

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


Bernard Heymans
Posts: 22
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".


[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"[/code]

Regards,

Bernard
Last edited by Bernard Heymans on Thu Nov 07, 2019 10:40 pm, edited 1 time in total.

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

If condition then Query1 else Query2

Postby Bernard Heymans » Thu Nov 07, 2019 8:42 pm

GRRRRR

Hello,

I face a problem where I would love to re-use what was created here... but this post seems to have been emptyed...

Any clues why ?

Bernard

CORRECTION: could edit the post... & save again
Last edited by Bernard Heymans on Thu Nov 07, 2019 10:41 pm, edited 1 time in total.

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

Re: [RE-OPEN] If condition then Query1 else Query2

Postby Bernard Heymans » Thu Nov 07, 2019 10:39 pm

I have found from the query created after this question... so solved agains.

The exemple here will activate a filter depending of the function fctn_Parameter("P_Nummer")



let
Bron = Access.Database(File.Contents(fctn_Parameter("Access")), [CreateNavigationProperties=true]),
_syn_UurRegistraties = Bron{[Schema="",Item="syn_UurRegistraties"]}[Data],

// here more steps

#"DELETE columns" = Table.RemoveColumns(#"ADDED col1",{"Duur(dagen)"}),
#"ProjectSelect" = Table.SelectRows(#"DELETE columns", each [Kostenplaatscode3] = fctn_Parameter("P_Nummer")),


IfStatement = if (fctn_Parameter("P_Nummer") ="All")
then #"DELETE columns"
else #"ProjectSelect",


#"Jaar ingevoegd" = Table.AddColumn(IfStatement, "Jaar", each Date.Year([Datum]), Int64.Type),
//here more steps
#"ColumnsRename" = Table.RenameColumns(#"Type gewijzigd2",{{"Kostenplaatscode3", "Project.nr"}, {"Kostenplaats3 omschr", "Project"}})
in
#"ColumnsRename"


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest