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

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
 

 

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

        You saved my day Thank you