Creating a Parameter that usess 3 outcomes or Results

[ccarpent]

Hi

I came across Chris Webb's video https://youtu.be/TpujELq3Iug  :courtesy of https://www.thebiccountant.com/ , which includes some points on Parameters in PowerQuery; I am doing this in Excels PowerQuery.  I seem to be half way there and can create a query to look up my source table which holds my 3 sets of dates.  The text in Blue is what I would like to do and is not valid code. So instead of using  for example on line 12 "Date.From(44165)", I would like to replace this value with my PowerQuey function which will retrieve the values assigned to "Get3Months", "Get8Month" and "Get3Months" and also do the same for the other referenecs I used e.g. "Date.From(#####)"

---------------------This is my Data Connection, it reads an excel workbook and I have added a custom column to indicate a Result; ">3 Months", "+3 Months", "-8 Motnhs", <8 Month" plus a final "Check" in case I have missed any logic.-----------

01. let
02. GetStartDate = (SomeValue as Date) =>
03. Get8Month = (SomeValue as Date =>
04. Get3Months = (Somevalue as Date =>

05. let
06. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
07. #"Changed Type" = Table.TransformColumnTypes(Source,{{Code", type text}, {"Start Date", type date}, {"End Date", type date}}),
08. #"Added Custom" = Table.AddColumn(#"Changed Type", "Result",

09. each if [End Date] = null
10. then "Not Include"
11. else
12. if [End Date]>Date.From(44165)
13. then ">3 Months"
14. else
15. if [End Date]<Date.From(43830)
16. then "<8 Months"
17. else
18. if List.AllTrue({[End Date]>=Date.From(44074),
19. [End Date]<=Date.From(44165)})
20. then "+3 Months"
21. else
22. if List.AllTrue({[End Date]>=Date.From(43830),
23. [End Date]<=Date.From(44074)})
24. then "-8 Months"
25. else
26. "check")
27. in
28. #"Added Custom"

-------------------------------------------------------------------------------

This is my Table, which is named as 'Parameters', and stores the 3 sets of dates. Again on the same workbook.

Parameter	Value	NumericValue
Start Date	31/08/2020	44074
WindowDate(-8 months)	31/12/2019	43830
WindowDate(+3 months)	30/11/2020	44165

My Query or Function which is named "GetDateValues" actually produces a list so it is reading my "Parameters" table.

let
ParamSource = Excel.CurrentWorkbook()
{[Name="Parameters"]} [Content],
#"Start_Date" = ParamSource{0}[Value],
#"-8 Months" = ParamSource{1}[Value],
#"+3 Months" = ParamSource{2}[Value]

in
({#"Start_Date", #"-8 Months",#"+3 Months"})

Screen Print:

Query Output

How do I go about intergrating my Parameters table with my main Query which will extract the values assigned the Parameters table x 3?

Many thanks.

Chris

[Anonymous]

if the need in your code is to calcualate the date 8 months before o 3 months after or similar you can get benefit from using the function

Date.AddMonths(dateTime as any, numberOfMonths as number) as any

for example

Date.AddMonths(#date(2020, 8, 31), -8)

results in

#date(2019, 12, 31)

or if you have you a variable wich contains your date value

myDate = #date(2020,8,31),

...

_8monthsBefMyDate = Date.AddMonths(myDate, -8),


...

someVariable= if [Date]<Date.AddMonths(myDate, -8) then "8 months before" else " not yet"

or other from library

[Jimmy801]

Hello @ccarpent 

if you need this data in 3 new column, then create a function with one paramater like this

(myParameter)=>

let

   .....

Then add 3 new columns where you are referencing to your 3 different parameters and your function like this

YourFunction(GetDateValues{0})

YourFunction(GetDateValues{1})

....

You could also think of creating one function with 3 parameters that produce again a list with 3 items. Then you could only add one new column, referencing to all your 3 parameters. In this case you will get a list in your new column.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ccarpent]

Hi Jimmy

Apology for taking a while to reply, very busy these days. 

I may need some extra help though.

This is my interpretation of what you mean but I am obviously not getting this right as it just keep showing me an error:

(myParameter)=>

let
ParamSource = Excel.CurrentWorkbook()
{[Name="Parameters"]} [Content],
ParamSource(GetDateValues{0}),
ParamSource(GetDateValues{1}),
ParamSource(GetDateValues{2})

in
({#"Start_Date", #"-8 Months",#"+3 Months"})

And this is me just updaing my orginal query called "GetDateValues"

Many thanks

Chris

[Jimmy801 · Accepted solution]

Hello @ccarpent 

I've now tried to create a table for parameter and then parameters that extract data out of this table with a Table.SelectRows. Then I'm using this parameters in a new column.  For sure there are other ways out.. to create a function that does the filtering within the function etc.

let  
    MyParameterTable = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JLCpRcEksSVXSUTI21Dew0DcyMDIAckxMDMxNlGJ1opXCM/NS8stBajR0LRRy8/NKMoo1IcoNjYDKDS1Byo0tjA3QlWsbIys30Dc0RJhuaGaqFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parameter = _t, Value = _t, NumericValue = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parameter", type text}, {"Value", type date}, {"NumericValue", Int64.Type}})
    in
        #"Changed Type",
    

    YourDataTable =   Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYyNtQzsNAzMgBzDE31DIxgHGMDPUMIJxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"End Date" = _t]),
    parGetStartDate = Table.SelectRows(MyParameterTable, each [Parameter]= "Start Date")[Value]{0},
    parGet8Months = Table.SelectRows(MyParameterTable, each [Parameter]= "WindowDate(-8 months)")[Value]{0},
        #"Changed Type" = Table.TransformColumnTypes(YourDataTable,{{"End Date", type date, "de-DE"}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [End Date] = null then "Not include" else if [End Date]>=parGet8Months then "- 8 months" else "etc.")

in 
    #"Added Custom"

HOpe this makes things a little clearer

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy