Multiple calls to the same dataflow

[snowrider1799]

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. 

[Burningsuit]

You can do this in Power Query, there's lots of information out there on exactly this issue.

some page to look at are:

Loop through Multiple Web Pages using Power Query

Iterating over multiple pages of web data using Power Query

Once you have this working thtough Power Query into a Dataset in Power BI, you can simply copy and paste the "M" code into the Dataflow.

hope this helps

Stuart

[lbendlin]

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]

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]

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 
    }
}