Power Query works great to extract data from websites. For example, I have a Joomla site that has a list of articles in a table. I wanted to summarize article hits per year.
Using Excel and Power Query:
- Fresh Excel sheet – Data->New Query Put in the path to the website, pick the correct table and load the data.
- Take time to set the correct data type for each column. This helps on later stages. Especially ensure that you pick whole numbers for columns that have whole numbers, datetype for such columns etc. Also, remove any unwanted columns etc.
- Save and close the query – and your data will be available in Excel. Now, create a PivotTable, join with other data etc.
This process would be very similar for PowerBI.