Workflow for update reports

[fep]

Hi I have made som reports and everytime I have to update I have to start from scratch.

My sources are excel files from different system.

I always end up changing the excel files before importing.

When I import the files with power query I make new columns.

I would like to find some tutorial on how the workflow from downloading to importing via query could be done in a more effective way.

[mahoneypat]

It hards to give specific guidance without seeing actual data; however, you likely need to create multiple tables in a snowflake schema from your data.  For example, you could create an Employees table that has a single row for each employee (use the Remove Duplicates button under Remove Rows when the Employee name or ID column is selected).  You can then have other tables that have the Employee name or ID column (for relationships with the Employees table) and one or more rows for each Employee and their data.  See this video for an example.

(1) Power BI Tutorial | From Flat File To Data Model - YouTube

Regards,

Pat

[mahoneypat]

Please give more information on the files you are working with (e.g., storage location, column names, sheet/table names) and what changes your are making manually, so an automated approach can be suggested.

Regards,

Pat

[fep]

Thank for you answer.

I have one excel file download from SAP that give me information on employees departement, navn, age, birthday, salary, startdate, job title etc.

I only need the name, departement, vacancy type and percent and age/birtday. But some employee is duplicates because the column that shoew education gives me two rows if the employee is registerede with two educations.

So I always clean this file in Excel. I would like to find a way to do this in Power BI.

Maybe something like this:

So IF the employee number , departement, vacancy percent and job title is equal, keep one row row and test that row keeped is not over 100% vacancy.

I have another files from a system that give me information on what subject and task the employee workhour is registered with. This information comes from a system used in a college to specify which subject the employee is set to teach and how many working hours is planned to use for this task.

This file I have to connect to SAP information. I can use employee number to match. But employee can work in other departements.

I think I can make some lookup table to sort this out. A tabell with information that has departement and subject.