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

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
     

6 Replies

  • 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