Challenge:
To create a dynamic report that measures time spent on specific projects daily by many team members. Each team member is required to enter their hours at the end of each day.
Report needs to be future proofed, eg, for the report to be flexible if more projects are added, the query has to be dynamic and then pull it in.
When importing files within a folder into Power BI, you can end up with something like this which requires you to expand the Data tab
Power BI then automatically creates the following step which hard code the name of the columns into the data extraction in the Changed Type Step.
However if I add column to my existing data source, the query will only import in the hard coded columns and miss off the new Project D and you would need to remember to go back and change the expand table step, if you were still working on the same file. Worse, it might be critical and not noticed.
Instead, you can use this code to expand the table by using Table.ColumnNames which takes the names of a list to create column names. So by referencing the previous step #”Removed Other Columns1”, using [Data] gives access to the data within the table and {0} gives you the first line of the data, which dynamically pull in column names which pull in Project D. Boom 😊.
