Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
klehar
Helper V
Helper V

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.