Forum Discussion

snowrider1799's avatar
snowrider1799
Helper I
6 years ago

Multiple calls to the same dataflow

Hi,

 

I have multiple datasets in a dataflow calling the same web API: e.g. https://www.abc.com/page=1, from page 1 to page 19 (and growing).

Each page consists of about 100k rows of data.

 

Each page has many columns, whereby some columns are expandable (records, list data type) to new rows. (in short, 1 same ID multiple rows of data).

I have tried putting in 19 datasets in a single dataflow, and it’s not working (endless validating queries). So I have to split into multiple dataflows with 5 datasets each.

 

Is there any method for the PBI Dataflows to retrieve all the data from the Web API, and store it, then we call into dataflows and perform ETL?

 

How to compose all 19 pages to the dataflows without having to wait for endless hours in validating queries? I need some lights shedding here.

 

On top of that, Records and List data type are not supported in dataflows as well, it will automatically change to text. 

Any solution to that?

I have uncheck the option to "Automatically detect column types & headers for unstructured sources", yet this issue still happens.

 

Thank you. 

Dataflow

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    Does it have to be Power BI?  Could you pull that web contents with curl or Powershell , place it into a single CSV file ( I can show you a simple Powershell script for that) and then feed that one file to your dataflow?

    • snowrider1799's avatar
      snowrider1799
      Helper I
      6 years ago

      Hi lbendlin ,

       

      I have 19 pages, and total about 2mil data, it will continue to grow in times.

       

      For dataflow #1, i am supposed to combine 19 pages with 5 columns (for example).

       

      Can this be done via the powershell? If yes, i will be interested to know. 

       

       

      • lbendlin's avatar
        lbendlin
        Super User
        6 years ago

        Here is a simple PowerShell script that concatenates any number of CSV files, stripping out the header row for all files except the first one.

         

        $Path = "C:\Users\xxx\Documents"
$Target = "your output file.csv"
$g = get-childItem "$Path\*.csv" 
$First = $true
foreach($f in $g) {
    if($First) {
        Get-Content "$Path\$f" | Add-Content $Target 
        $First = $false
    } else {
       Get-Content "$Path\$f" | Select -Skip 1 | Add-Content $Target 
    }
}