Forum Discussion

klehar's avatar
klehar
Helper V
5 years ago
Solved

Use table values as a parameter

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 i...
Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    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
     
klehar's avatar
klehar
Helper V
5 years ago

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's avatar
selimovd
Most Valuable Professional
5 years ago

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