30.7 Folders, Parameters, Databases and Web

Q72. Explain the helper queries created when you combine files from a folder.

Parameter1 (a binary parameter), Sample File (the first file), Transform Sample File (where you write per-file cleaning steps) and Transform File (a function generated from it, called for every file). The combined query calls the function and adds Source.Name.

Q73. How do you handle a monthly file that has an extra column or a renamed header?

In Transform Sample File, rename alternative headers with Table.RenameColumns(..., MissingField.Ignore), and keep a fixed column list with Table.SelectColumns(..., MissingField.UseNull). Also check the combined query's Changed Type step for references to missing columns.

Q74. A new monthly file is dropped into the folder. What do you need to do?

Nothing in the query. Refresh picks it up, provided it matches the filters (extension, name pattern). For scheduled refresh in the Service, a local folder needs an on-premises data gateway, while a SharePoint folder does not.

Q75. What are Power Query parameters used for?

To avoid hard-coding values such as folder paths, server/database names and date ranges. They make Dev → Prod switching and templates (.pbit) easy, and RangeStart/RangeEnd parameters are required for incremental refresh.

Q76. What is the difference between a Power Query parameter, a what-if parameter and a field parameter?

Power Query parameters change what data is loaded at refresh (set by the author). What-if parameters create a numeric table and a SELECTEDVALUE measure that users change with a slicer. Field parameters let users switch the measures or columns shown in a visual.

Q77. What is a .pbit file?

A Power BI template containing the report, model, queries and parameters but no data. When it is opened, the user is prompted for parameter values and the data is loaded.

Q78. How do you connect to SQL Server and what authentication options exist?

Get data › SQL Server › server (and optional database) › Import or DirectQuery › credentials: Windows (current or alternate), Database (SQL login) or Microsoft account (Entra ID). Then select tables in the Navigator and Transform Data.

Q79. Why is writing a native SQL statement sometimes discouraged?

Steps added after a native query usually cannot fold, so later filters and merges run in Power BI instead of the database. Prefer the Navigator or database views, or put all filtering inside the SQL.

Q80. What do you need before connecting Power BI to MySQL?

The MySQL Connector/NET driver installed on the machine (and on the gateway machine for Service refresh), the server:port, database name, and a MySQL user with permissions from your host.

Q81. DirectQuery vs Live connection – what is the difference?

DirectQuery connects to a data source (such as SQL Server), and you build the model in your file. Queries run against the database. A Live connection connects to an existing semantic model (Power BI or Analysis Services). You build only the report, with no Power Query and only report-level measures.

Q82. What are privacy levels and what is a Formula.Firewall error?

Privacy levels (Private, Organizational, Public) control whether data from one source can be sent to another while combining queries. Formula.Firewall errors appear when queries combine sources in a way that could leak data. Fix them by setting correct levels or restructuring the queries.

Q83. How do you scrape a table from a web page in Power BI?

Get data › Web › URL › choose an HTML or suggested table in the Navigator (or use Add table using examples) › Transform Data › clean it. Respect the site's terms of use, and remember that layout changes can break the query.

Q84. How do you load many web pages or API pages?

Build a query for one page, parameterise the page number, create a function, generate a list of pages ({1..N}), and use Invoke Custom Function, then expand. Use Web.Contents with a fixed base URL plus RelativePath/Query so Service refresh can validate the data source.

Q85. How can you bring Google Sheets data into Power BI?

Use the Google Sheets connector (sign in with Google, paste the sheet URL), or publish the sheet as CSV and read the link with the Web connector. The second method makes the data public, so use it only for non-sensitive data.