Forum Discussion
My database has one row per week
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
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 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
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
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!