Forum Discussion
My database has one row per week
Hello,
I am faced with a complex situation. I have an Excel database that includes all the consultants in my company and the mission they are assigned to. Each line represents a consultant, his mission, the type of contract. And each column represents a week of the year. Thus, I have 144 consultants, 144 lines. And there are 52 columns, one for each week as well as 6 other columns (type of contract, mission, hierarchical superior etc.). As you might expect, when doing my PowerBI, I would like to be able to filter by week to know my number of consultants, number of entries and exits per week. However, since my weeks are my columns, it will make me 52 different fields, I will not be able to filter by column on PowerBI. Here is an example of my database.
As you can see, I have 58 columns and 144 rows. There is another problem: If one of my consultants changes clients, we duplicate his line (like BB here) to add a line with the name of the new client following the history of the weeks.
I wanted to know if there was a simple way to use this data in PowerBI to filter against weeks. I had considered rebuilding the entire database by adding a weekly column and duplicating 52 times each row, but this would make me recreate a table with 52*144 lines. There must be an easier way?
Tell me if it's not clear. Thank you in advance.
Have a good day.
The query editor has an option that will "un povit" your week columns.
1) open the table in the query editor
2) select all 52 week columns
3) select unpovit only selected columns from unpovit options
That will solve your first problem.
then select the column with blank values and use the remove empty optionthat should let you get rid of the "empty" values for the weeks when a consultant changed clients
I learn something every time I answer a question
7 Replies
- kentyler
Solution Sage
The query editor has an option that will "un povit" your week columns.
1) open the table in the query editor
2) select all 52 week columns
3) select unpovit only selected columns from unpovit options
That will solve your first problem.
then select the column with blank values and use the remove empty optionthat should let you get rid of the "empty" values for the weeks when a consultant changed clients
I learn something every time I answer a question
Thanks! You're the best. Additionnal question, if my table is like this:
Can I make something to keep the date. In fact, if people doesn't know what is the date of the week 1 etc. The process is changing? I have one table like this per year in Excel, it is possible to merge all these things? Or I have to make one rapport per year? Thanks a lot! I really appreciate your help!
- kentyler
You would need to add a "year" column to your spreadsheet.
Then you would create a lookup table that had one entry for each week, and the part of the date that was the month/day
You create a relationship between this table and the imported spreadsheet
This relationship will be on the "week" value, ie "week1", etc
The relationship will let you import data from any year, and create a calculated column that combines the month/day for the "week" with the year column and transform it into a real date
That would mean you could put the spreadsheet for each year (assuming they always have the same column names, and always include a year column) in a folder, and then use Power Bi's ability to use a folder as a data source, which will automatically append all the spreadsheets int the folder into one table.... so every year you just add a new file to the folder.
I learn something every time I answer a question
I'm a personal Power BI trainer