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

[DennisGaida]

I'm intrigued by the text analytics (https://docs.microsoft.com/en-us/azure/cognitive-services/text-analytics/tutorials/tutorial-power-bi...) example. Problem is, it is expensive for a lot of data, I would only want the example to run once over all my data, then be static.

PowerBI wants to refresh data upon changing the model this in turn would require me to query text analytics again for every model change - I don't want that, because maybe I just added a column to that particular query.

Let's say I have a table like so:

ID  Data
1   This is great news
2   Awesome news
3   Good news
4   Bad News

I tried creating a reference table and added columns with custom functions., but I get the following error: Formula.Firewall: Query 'test_data2_AI' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Adding "regular" columns just works. Adding the custom function column to the original query also works just fine.

How do I add the custom function columns not to the main query, but to a related query? This way I can refresh the main query all the time (and e.g. add columns), but disable refresh for the related query? What is the best practice here? I don't want to execute the custom function for each data refresh, but only once for each row.

---

Based upon this post: https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-n... I understand I would need to source my base data multiple times?

I got it working by just sourcing my data two times - one time is my "main query" with transformations and everything PowerBI does. The other query just uses the data to do the AI webservice calculations. This means I can set the webservice query to not refresh on report refresh, the main query can refresh as much as it wants?

[v-yuezhe-msft]

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

@v-yuezhe-msftI 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.

[v-yuezhe-msft]

@DennisGaida,

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

Regards,
Lydia

[DennisGaida]

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.