Use table values as a parameter

[klehar]

Hi,

I'm trying to add months to my date column. But at this moment it is static

I want that to come from my table called 'Table' and column called Dealy Factor.

So if delay factor is changed to 3 then this will add 3 months to my date

FYI : My delay factor comes from my sharepoint list where the users input the delay factor as a parameter

In my actual dataset this table contains a lot of parameters but for simplicity I have included just the 'Delay Factor'

Find the link of file here

[selimovd]

Hey @klehar ,

you can do that in 2 steps:

1. Go to your "Table", do a right click on your number and chose drill down:

2. Instead of a table this query will show a number, also the symbol will change:

3. Then go to your second "Table (2)" and change the fixed number "2" to "Table":

Congratulations, your added date is now dynamically, based on the result of another query 😊

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[klehar]

@selimovd  thanks for the quick reply

However as I told you my parameter table has multiple columns with single row

Drilling it down will create a list and not a table

I guess i have to use list functions to fetch some values from each column but not sure how

[selimovd · Accepted solution]

Hello @klehar ,

you didn't say they are in one table 😉

You can just do the drill down for each parameter in the second query.

For your example, I added 2 more columns:

In the "Table (2)" query you can add the drilldown as an own variable, like that:

ParameterDelayFactor = Table{0}[Delay Factor],

Or if you need the Parameter 3:

ParameterParameter3 = Table{0}[Parameter 3],

Then you can add it to your normal query of "Table (2)":

let
    ParameterDelayFactor = Table{0}[Delay Factor],
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE31DcyMDJQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateAdd", each Date.AddMonths ( [Date], ParameterDelayFactor )),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"DateAdd", type date}})
in
    #"Changed Type1"

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[klehar]

You saved my day Thank you

[klehar]

@selimovd can you send me the file please

[selimovd]

@klehar 

Sure, here you go:

https://www.swisstransfer.com/d/57685d34-a1b8-4533-81e8-d1359c818e16

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic