Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to convert a query to a parameter?

Hi everyone, I have two queries A and B, query A is my main data source and query B is a json file that I use for both power BI and a python application we use here.

 

I would like to know how I can import just one field from my query B and convert it to a parameter to filter the data from my query A.

 

I can do this with a parameter manually changing the value when necessary, but I would like to do this through my json file, because that way would update other reports that use the same parameter.

Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    I put it together in one query, but I think it demostrates the idea:

    let 
    B = 
        let
            Source = "{""a"":1}",
            Use = Json.Document("{""name"":""John"", ""age"":30, ""car"":null}")[age]
        in Use,
    A = 
        let             
            t = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQitWJVjKCUMYQygRIxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ages = _t]),
            #"Filtered Rows" = Table.SelectRows(t, each (Number.From([Ages]) = B))
        in #"Filtered Rows"
in A

    Above query B returns a number (age) from the JSON string and then query A refers to query B (essentially, uses the number it returns) to use as a filter.

     

    Cheers,

    John

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Greg_Deckler  I did as indicated, but my parameter is empty.

      I would like that single item in the list to become the value of my parameter.

      Is it possible to do this?

      • jbwtp's avatar
        jbwtp
        Memorable Member
        3 years ago

        I put it together in one query, but I think it demostrates the idea:

        let 
    B = 
        let
            Source = "{""a"":1}",
            Use = Json.Document("{""name"":""John"", ""age"":30, ""car"":null}")[age]
        in Use,
    A = 
        let             
            t = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQitWJVjKCUMYQygRIxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ages = _t]),
            #"Filtered Rows" = Table.SelectRows(t, each (Number.From([Ages]) = B))
        in #"Filtered Rows"
in A

        Above query B returns a number (age) from the JSON string and then query A refers to query B (essentially, uses the number it returns) to use as a filter.

         

        Cheers,

        John