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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
GAPER
Helper V
Helper V

Snowflake Dynamic Query failed

Here is the code. The error I got is 

 

Expression.Error: We cannot convert a value of type Record to type Text.
Details:
Value=[Record]
Type=[Type]

 

I am trying to create a dynamic query where i can use a filter in the PowerBI UI to determine what dates i am returning from snowflake. Thanks!

 

let
    selectedDates = if Type.Is(Value.Type(DateMParameter), List.Type) then 
      Text.Combine({"'", Text.Combine(DateMParameter, "','") , "'"})
    else
      Text.Combine({"'" , DateMParameter , "'"}),

    selectAllCountries = if Type.Is(Value.Type(DateMParameter), List.Type) then 
      List.Contains(DateMParameter, "__SelectAll__")
    else
      false,

    query = "SELECT #(lf)*#(lf)#(lf)FROM LANDING.HIDDEN.POWERBIPROJECTSUMMARY",

    dynamicFilter = if selectAllDates then " "
                    else  Text.Combine({" WHERE Date in(", selectedDate, ")"}),

    finalQuery = Text.Combine({query, dynamicFilter}),

    Source = Value.NativeQuery(Snowflake.Databases("HIDDEN.southeast-asia.azure.snowflakecomputing.com","POWERBI_WH"){[Name="LANDING"]}[Data], [Query=finalQuery], null, [EnableFolding=true])

in
    Source

 

1 REPLY 1
jpessoa8
Super User
Super User

Hi @GAPER,

 

The issue will probably be related with DateMParameter.. Can you provide it's query and/or results?


Best regards,
Jorge Pessoa

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.