Forum Discussion

zhanna27's avatar
zhanna27
Frequent Visitor
2 years ago

PowerBI Service Dataflow & Datamart & PowerBI Desktop

Hello,  I am new to this community and I have started recently the PowerBI Desktop & Service skills. I am struggling right now because I have to download 3 static archived heavy datasets .csv for th...
Need Help
zhanna27's avatar
zhanna27
Frequent Visitor
2 years ago

Dear Anonymous, 

thank you for your quick reply. I appreciate your tips. Again that's a good solution of yours which you propose for dynamic data to be stored in a file in my sharepoint desktop folder. Indeed, I haven't done any storage of this updated data because I thought it could be appended directly to a dataflow dataset and then the updated dataflow dataset could be used by datamarts directly and could be saved in datamarts each day (then the next day, it would upload archived data would delete already present archived data with a "remove_duplicates" step and + would upload just a new date). I understood now that dataflow doesn't save documents but I thought datamarts did so. So my update function here below is supposed to update and append new data into an archived dataset each day but only in dataflows : 

 

let
    // Function to retrieve data from the API
    Limit = 10000,
    Query = (offset) =>
    let
    Source = Json.Document(Web.Contents("https://api.url_base", [
    RelativePath = "some_endpoint.json",
    Query = [
    limit = Number.ToText(Limit),
    offset = Number.ToText(offset),
    auth = "my_token",
    startDate = Date.ToText(DateTime.Date(Date.AddDays(DateTime.LocalNow(), -2)), "yyy-MM-dd"),
    endDate = Date.ToText(DateTime.Date(Date.AddDays(DateTime.LocalNow(), -1)), "yyy-MM-dd")
    ]
    ]))
in
    Source,
// Call the function with an offset of 0
  SourceAPI = QueryAPI(0),
  // Convert the JSON data to a table
  TableAPI = Table.FromList(SourceAPI, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  // Expand the record column
  ExpandedAPI = Table.ExpandRecordColumn(TableAPI, "Column1", {"....."}, {"....."}),
  // Change column types
  ChangedTypeAPI = Table.TransformColumnTypes(ExpandedAPI, {{"....}}),
  // Replace errors
  ReplacedErrorsAPI = Table.ReplaceErrorValues(ChangedTypeAPI, {{.....}),
  // Remove duplicates
  RemovedDuplicatesAPI = Table.Distinct(ReplacedErrorsAPI),
  // Retrieve data from the CSV file
  SourceCSV = Csv.Document(Web.Contents("https://company-sharepoint.com/my_account/documents/desktop/2010-2023_folder/my_archived_file_2010_2023.csv"), [Delimiter = ",", Columns = 10, QuoteStyle = QuoteStyle.None]),
  PromotedHeadersCSV = Table.PromoteHeaders(SourceCSV, [PromoteAllScalars = true]),
  ChangedColumnTypeCSV = Table.TransformColumnTypes(PromotedHeadersCSV, {{".....}}),
  AppendedQueries = Table.Combine({ChangedColumnTypeCSV, RemovedDuplicatesAPI}),
  RemovedDuplicatesCSV = Table.Distinct(AppendedQueries),
  #"Transform columns" = Table.TransformColumnTypes(RemovedDuplicatesCSV, {{"...}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"...}}),
  #"Sorted rows" = Table.Sort(#"Replace errors", {{"localTime", Order.Descending}})
in
  #"Sorted rows"
 
But I don't know how to automate the process so that I don't do the update manually everyday by myself. Do you have any suggestions ? There should be some tool that can help me automate. 
So you confirm that no possible storage in powerbi service inside ? No Azure datalake or something going with the power BI service together ? Other option would be probably to store the data in some database which can get updated automatically calling APIs. What do you think ? If I could exchange in live that would be easier to explain 😉 have a nice day !! and thanks again !!
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi zhanna27 , ok, sorry I missed the part where you said in the original post that the dynamic data is loaded from an API rather than CSV files. So, is it an API call to a database or something else? Can you query more data through the API than just a single day? I can see in your function you are querying the data based on two dates, so can't you just query the data since the static data ends (mid-Dec 23)? If there is too much data for the API to handle, you could probably multiply the queries and load, e.g. a month at a time. Also, if the data is coming from a Database, can't you just use the DB (e.g. SQL connector in Dataflow, so then you can use normal SQL query to import all the data you need)? This would be probably the simplest solution as an SQL query should handle millions of rows of data, so you could probably do it in a single query.

I assume that from time to time data which you currently loading dynamically are dumped to the csv files (e.g. every year?) so then you would need to amend the dynamic data query date range.

As for the storage options, I don't think there is anything like that in PowerBI itself. I am not sure why specifically you are using Datamarts. In my experience, Dataflows work faster when loading data, and I don't find them very useful apart from one single feature, which gives you an SQL endpoint to query the data from Datamart.

You could use a database to store the data, but first you would still need to extract the data from your API. There are a couple of ways how you could automate this depending on what tools you have access to. One example might be to use PowerAutomate, which can run on a schedule every day and export the data from API and either upload it to a Database, Datamart, or SharePoint List or just save it as a .csv or .xlsx file where from you can load the data into your 'dynamic' dataflow. However, there would be some limits on the amount of data you can handle with PowerAutomate, so if you have millions of rows every day, this may not work. Another more advanced solution would be to use Azure DataFactory (if you have access to Azure). In the ADF, you can create a pipeline which will run every day, export the data from API and load it into, for example, the Azure SQL database.