cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
klehar
Helper IV
Helper IV

Use table values as a parameter

Hi,

 

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

 

klehar_1-1619765123644.png

 

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

 

klehar_2-1619765237470.png

 

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

1 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:

selimovd_0-1619767499955.png

 

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
 

View solution in original post

6 REPLIES 6
selimovd
Super User
Super User

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:

selimovd_0-1619766626658.png

 

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

selimovd_1-1619766666324.png

 

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

selimovd_2-1619766702099.png

 

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
 

 

@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

klehar_0-1619767011226.png

 

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:

selimovd_0-1619767499955.png

 

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
 

You saved my day Thank you

@selimovd can you send me the file please

@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
 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors