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 SageYou 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!
- 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.
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 [email protected] and I'll send you a link to a zoom screen share and we can talk through it.