Forum Discussion

DennisGaida's avatar
DennisGaida
Frequent Visitor
8 years ago

How to use custom function to fill another query (main_data & main_data_additial)?

I'm intrigued by the text analytics (https://docs.microsoft.com/en-us/azure/cognitive-services/text-analytics/tutorials/tutorial-power-bi-key-phrases) example. Problem is, it is expensive for a lot o...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

DennisGaida,

When you reference a table in query editor of Power BI Desktop, each time the main table refreshes after you click "Refresh Preview" in Query Editor, the related table will be refreshed.

If you want to disable refresh for the related table in report view of Power BI Desktop, you can right-click the related table in Query Editor , then disable "Include in report refresh".


Regards,
Lydia

DennisGaida's avatar
DennisGaida
Frequent Visitor
8 years ago

AnonymousI maybe phrased my question too complicated.

 

What is the best practice architecture for the following layout:

 

  • Static text data that doesn't change, just as in the table above
  • I want to run Azure text analytics via custom function
  • I do not want to refresh the Azure text analytics results after I received them once

I want to be able to refresh my table with new columns, I never want to refresh the column results from Azure (once the text was sent to Azure it doesn'T change / doesn'T need to be re-analyzed). PowerBI does just that on refreshing queries: It queries Azure for each row in the query again.

 

Now my question: How do I split my query into let's say "main_data" and "main_data_additional" (for the Azure text analytics results). I can't use references, because I cannot call external sources with a referenced table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    DennisGaida,

    Could you please post the code in the Advanced Editor of your currect query here?

    Regards,
    Lydia

    • DennisGaida's avatar
      DennisGaida
      Frequent Visitor
      8 years ago
      let
    Source = Json.Document(File.Contents("C:\data\message.json")),
    messages = Source[messages],
    #"Converted to Table" = Table.FromList(messages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"timestamp", "content"}, { "Column1.timestamp", "Column1.content"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each [Column1.content] <> null),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1.content", "Content"}, {"Column1.timestamp", "Timestamp"}}),
    #"Kept First Rows" = Table.FirstN(#"Renamed Columns",20),
    #"Invoked Custom Function" = Table.AddColumn(#"Kept First Rows", "KeyPhrases", each AI_KeyPhrases([Content])),
    #"Invoked Custom Function1" = Table.AddColumn(#"Invoked Custom Function", "Sentiment", each AI_Sentiment([Content])),
    #"Changed Type" = Table.TransformColumnTypes(#"Invoked Custom Function1",{{"Sentiment", type number}, {"KeyPhrases", type text}, {"Timestamp", Int64.Type}, {"Content", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Sentiment", type number}}, "de-DE"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Sentiment100", each [Sentiment]*100),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Sentiment100", type number}})
in
    #"Changed Type1"

      The queries for the Azure Cognitive Services functions are pretty much the same as in the article posted in the original message.