Unable to Combine data sharepoint, web.contents, power automate

[username2345]

I have a dataset that collects data from a web api and sharepoint csv and excel files and also an sql database. 

These datasets all work fine and combine well in power bi service.

My issue comes when i join all these queries and have to pass the data from the queries to a power automate API

let
SourceTable =  Table.Combine({Table.SelectColumns(AllDataBridge , {"Data"})}),
    #"Trimmed Text" = Table.TransformColumns(SourceTable,{{"Data", Text.Trim, type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Trimmed Text",{{"Data", Text.Upper, type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Uppercased Text"),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Duplicates",null,null,(a,b,c) => [Search = a] ,{"Data"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type),
    #"Inserted Range" = Table.AddColumn(#"Added Index", "Range", each let rangeSize = 13, offset = 1, inclusive = false, rangeIndex = Number.RoundDown(([Index] - offset) / rangeSize) in Text.From(rangeIndex * rangeSize + offset, "en-GB") & "-" & Text.From((rangeIndex + 1) * rangeSize + offset - (if inclusive then 1 else 0), "en-GB"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Range",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Range"}, {{"SearchData", each _, type table [Data=text, Range=text]}}),
    #"Search List" = Table.AddColumn(#"Grouped Rows", "Search", each [SearchData][Data] ),
    #"Binary To Parse Through" = Table.ReplaceValue(#"Search List",null,null,(a,b,c) =>Json.FromValue([Search = a]) ,{"Search"}),
    #"Call PowerAutomate API" = Table.AddColumn(#"Binary To Parse Through", "APICalls", each try Web.Contents("https://prod-88.westeurope.logic.azure.com:443/......", [Headers=[#"Content-Type"="application/json"], Timeout=#duration(0, 0, 10, 0),Content=[Data]]) otherwise "Retry")
in
    #"Call PowerAutomate API"

 The query above references a query that has cleaned data from all the sources and filtered down the information that i need to search. 

The step that's the issue is the last web.contents step that is a post method to a power automate to run a flow and return a response. 

The flow calls 3 api's and with one of them it loops and collects data till no data is left then sends a json response to power bi. I originally tried this in power query but the looping and collecting data was very difficult and would stall.

But I tested this with ignoring privacy levels and when I turned them back on to see if it'd work in service there was no luck

I have tried all solutions recommended for this...

Making the query just 1 massive query,

Splitting the queries and then joining them,

Creating the step as a custom function.

I am assuming it doesn't like that I am sending the data to the logic app url but it is a microsoft logic url.

Could anyone try and help with how best to tackle this?

[lbendlin]

This is rather dangerous. Are you aware that any given Power Query can be run multiple times? All the Meta data sync stuff etc. That is one of the reasons why data writeback attempts should not be made in Power Query.

Keep in mind that Power BI is a reporting application.  Yes, Power Query has some elements of ETL, but it is not comparable to SSIS or Informatica etc. Power BI/Power Query is likely not the right tool for your highly volatile process.

[username2345]

Hi, 
The api's I am using are all post api's so in order to return the data. I will try and see another way to go about the process because I can see power query is not loving it