My database has one row per week

[Anonymous]

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.

[kentyler · Accepted solution]

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 option

that 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

I'm a personal power bi trainer 

[Anonymous]

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 

[Anonymous]

Thanks! Your answer helps me a lot! Last question (I hope!). In my case, now, in Power BI, I have 4 databases: 

- Database 2018 on my company (with all workers and clients)

- Database week 2018 (With week and day).

- Database 2019 on my company.

- Database week 2019. 

2018 and 2019 have the same headers. It is possible to make only one database for 2018 and 2019 (known both are the same headers). I have tried the append function, it worked BUT my created row are impacted. Indeed, the "Year" row put only 2019 if I append 2019 with 2018 or 2018 if I append 2018 with 2019. How to solve it? Unless, I have to created one report by year. Thanks! 

[kentyler]

Glad to help.

I think you are saying you have 4 tables.

To append your 2018 data to your 2019 data you will have to append only the NON canclulated rows. If you append the non calculated rows from 2018 to 2019, then the calculated rows in your 2019 table should do the same calculations for the rows from the 2018 table.
You can then add a year column to your 2 database week tables and append 2018 to 2019.
Even if you have to remove all your calculated fields to do the append, and then recreate them, it will be much better to combine the data with the year in a year column than to have to set up a new table for every year. 

You will find that after you have a few years data you can also do interesting reports that compare year to year statistics.

[Anonymous]

Thanks a lot! But I have a little doubt: Can I append only NON calculated row? In fact, doesn't forget that in my table I have only week1, week2 etc. So there is exactly the same in 2018 and 2019, I have no date in my table juste the number of the week so if I merge all these things, I will be lost to know which is 2018 and which is 2019, no? Thanks!

[kentyler]

Sometimes these things get hard to explain in messages. Send me an email at ken@8thfold.com and I'll send you a link to a zoom screen share and we can talk through it.