Forum Discussion

des's avatar
des
Icon for Helper I rankHelper I
6 years ago

Power BI Report Web scraping Incrementally

Hi,

I have searched through forums and not been able to find anything dealing specifically with what I want to do, I have found things that are sort of similar but missing some essential components that would solve my specific problem.

I am using latest version of power BI desktop  (As of the date of this posting) for dev with all preview features switched on. 

The scenario:

1.  I am using the  "Web" connector to get my data from a website.

2.  I have a website that I am scraping data from.  I enter the url, in said conector, in that webiste there is a table with three columns A, B and C.  I am able to successfully load said columns into a table and refresh them in power query with no issue.  One row is available and loaded from the website table on refesh, the row data is updated daily on the website.  The intention is for said single row to be loaded each day to Power BI report in an accumilative fashion.

3.  In order to keep track of the day on which said row was loaded I have added column D a date stamp column defined by:  DateTime.Date(DateTime.LocalNow()) of type datetime. There is no date data in the source data that could be used for this purpose.  I would like to increment this date for each row loaded.

4.  Essentially I want power bi to load the data one row at a time daily.  Expected behaviour is for it to reload all the data which in effect overwrites the prior days data leaving me with a one row dataset at any given time.  Instead, I want to do this loading one row at a time, incrementally. 

 

It feels like this should be asy to do, but I can't see any easy obvious way to do this.  I have looked at the incremental load feature released by microsoft recently for Power BI Pro, but all these solutions seem to require a date of some sourt in the source data, which can leverage paramatised start and end dates to partition and filter the source data to facilitate an incremental load.

I am aware that there are other ways to deal with this by handeling the incremental par tof the load in a relational data store, in data flows or whatever, this question however, is about how it is possible to do it completely in Power BI if indeed it is even possible.

thanks,

D

Need Help

3 Replies

    • des's avatar
      des
      Icon for Helper I rankHelper I
      6 years ago

      Workarounds perhaps?

  • DeepaliChakre's avatar
    DeepaliChakre
    New Member
    2 years ago

    Hi des,

     

    I'm not sure whether you are still seeking the solution.

    I tried to achieve the same with PowerBi and scoured the internet, but I found nothing.

    I am mostly working on Qliksense and did what I would have done with Qlik QVDs - concat the existing data.

    In the M query, after my web scrapping script, I added a Python script to store my data in a Txt file.

    In the next run of my web scrapping, concatenate to the existing data in the Txt file.

    Here is my sample M query,

    ExportPython = Python.Execute("
import os
import pandas as pd

# 'dataset' variable contains the Power Query table
df = dataset

file_path = 'file\path\'

# Check if the file exists
if os.path.exists(file_path):
# If file exists, read the existing data
df1 = pd.read_csv(file_path, sep='\t')
else:
# If file doesn't exist, create an empty DataFrame with the same columns as df
df1 = pd.DataFrame(columns=df.columns)

# Concatenate the new data (df) with the existing data (df1)
df2 = pd.concat([df1, df], ignore_index=True)
df2 = df2.drop_duplicates()# Export the combined data to a text file (tab-separated values)
df2.to_csv(filepath, sep='\t', index=False)
del df, df1
", [dataset = #"Renamed Columns2"])

     


    To handle a large volume of data, you create multiple Txt files once they reach the max file size and read them in a loop. Python will give you a lot of flexibility.