Hi all,
In Power BI desktop I have PBIX file, that is working fine and I can publish the file to our workspace.
The file is extracting data as a RestAPI connection
But when I want to create a scheduled refresh it seems like it is not working.
I then have created a dataflow, and as long as I only try to get data with a GET request it seems like I can create a dataflow, which will update on scheduled refresh hours.
But one table has to be extracted with a POST request, which is possible in Power Query window of the dataflow, but not to include in the scheduled refresh.
Does anybody have a solution for that? (query code included below)
let
// Function to convert a datetime to Unix timestamp (seconds since 1 Jan 1970)
ConvertToUnixTime = (inputDate as datetime) =>
let
unixEpochStart = #datetime(1970, 1, 1, 0, 0, 0),
duration = Duration.From(inputDate - unixEpochStart),
// Convert the duration to total seconds
totalSeconds = Duration.Days(duration) * 86400 + Duration.Hours(duration) * 3600 + Duration.Minutes(duration) * 60 + Duration.Seconds(duration)
in
totalSeconds,
// Define input parameters for startdate and enddate
startDate = #datetime(2024, 1, 1, 0, 0, 0), // Input your start date here
endDate = #datetime(2024, 1, 31, 23, 59, 59), // Input your end date here
// Convert start and end dates to Unix timestamps
fromUnixTime = ConvertToUnixTime(startDate),
toUnixTime = ConvertToUnixTime(endDate),
// Function to get data with a given offset
GetDataWithOffset = (fromUnixTime as number, toUnixTime as number, offsetValue as number) =>
let
// Define body with the Unix timestamps and offset
body = "from=" & Text.From(fromUnixTime) & "&to=" & Text.From(toUnixTime) & "&offset=" & Text.From(offsetValue), // Body as URL-encoded parameters
// Define headers
headers = [
firmaid = FirmaId,
token = Token,
Accept = accept,
#"Content-Type" = contentType // Content-Type must be x-www-form-urlencoded
],
// Try to send POST request
response = try
Json.Document(Web.Contents("https://api.onlinepos.dk/api/exportKoncern", [
Headers = headers,
Content = Text.ToBinary(body) // Send body as URL-encoded data
]))
otherwise
let
errorMessage = try Web.Contents("https://api.onlinepos.dk/api/exportKoncern", [Headers = headers, Content = Text.ToBinary(body)]) otherwise null,
decodedMessage = if errorMessage <> null then Text.FromBinary(errorMessage) else "No response"
in
"Request error: " & decodedMessage,
// Return the result
result = response,
sales = result[sales],
#"Converted to Table" = Table.FromList(sales, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table",
// Get data in 10 iterations (looping with offset)
HentMånedensSalgsordrer = List.Transform({0..11}, each GetDataWithOffset(fromUnixTime, toUnixTime, _ * 1000)),
// Combine all data into one table
KonverterListeTilTabel = Table.FromList(HentMånedensSalgsordrer, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
KonverterTilSalgslinjer = Table.ExpandTableColumn(KonverterListeTilTabel, "Column1", {"Column1"}, {"Salgslinjer"}),
HentLinjerne = Table.ExpandRecordColumn(KonverterTilSalgslinjer, "Salgslinjer", {"line"}, {"line"}),
UdvidTabelTilAtViseAllePosterUdvalgtekolonner = Table.ExpandRecordColumn(HentLinjerne, "line", {"firma_id", "chk", "bo_chk", "date", "time", "product_id", "product", "product_group_id", "product_group", "MasterID", "amount", "price", "payment_type", "clerk", "table", "discount"}, {"firma_id", "chk", "bo_chk", "date", "time", "product_id", "product", "product_group_id", "product_group", "MasterID", "amount", "price", "payment_type", "clerk", "table", "discount"}),
// Change column types for data
SkiftDataTypeAlleKolonner = Table.TransformColumnTypes(UdvidTabelTilAtViseAllePosterUdvalgtekolonner,{{"date", type date}, {"price", type number}, {"clerk", Int64.Type}, {"firma_id", Int64.Type}, {"chk", Int64.Type}, {"bo_chk", Int64.Type}, {"time", type time}, {"product_id", Int64.Type}, {"product", type text}, {"product_group_id", Int64.Type}, {"product_group", type text}, {"MasterID", Int64.Type}, {"amount", type number}, {"payment_type", type text}, {"table", Int64.Type}, {"discount", type number}})
in
SkiftDataTypeAlleKolonner
