Forum Discussion
Using Multiple Identical Data Sets for the Same Visuals
Hello,
I am new to the forums and I have looked around and not had any luck answering this question.
I will explain in a little more detail:
I have a large dataset I would like to break up by month. An example of the data would be as follows:
Date Name ID Country Region # of Sales Total Revenue
I would like to bring this data in through several different CSV files (by month) and create visuals as if it were 1 large dataset. So if i had 6 sets of data for January through June and i wanted to make a bar chart for total sales by month - would that be possible?
Please let me know if there is already a thread explaining this - if it is possible and how i would go about formatting it.
Thanks!
You can append the data
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
if needed
https://radacad.com/pivot-and-unpivot-with-power-bi
Preferably convert Month into month start or end date and use date dimension that will make time intelligence easy.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Appreciate your Kudos. mark me with @ for more information
4 Replies
You can append the data
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
if needed
https://radacad.com/pivot-and-unpivot-with-power-bi
Preferably convert Month into month start or end date and use date dimension that will make time intelligence easy.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Appreciate your Kudos. mark me with @ for more information
You could use an Append query but the easiest thing to do would be to put all the CSV files in a folder and use a Folder query.
Hi Anonymous ,
Yes, append queries is an easiest and fastest way to achieve it:
Go to Home>edit queries:
You can choose append queries as new if you wanna keep the previous queries:
Here is a related reference.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!Hi,
Try this
- Append data from the multiple CSV files
- Create a Calendar Table and build a relationship from the Date column of the appended dataset to the Date column of the Calendar Table
- In the Calendar Table, write calculated column formulas to extract Year and Month: Year = year(calendar[Date]), Month Name = FORMAT(Calendar[Date],"mmmm") and Month Number = MONTH(Calendar[Date])
- Click on any one cell in the Month Name column and then go to Sort by column > Month Number
- To your visual, drag Year and Month from the Calendar Table.
- Write this measure
Revenue = SUM(Data[Total revenue])
Hope this helps.
