Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
SachinC
Helper V
Helper V

DAX Append Query - Merging Data

Hi,

I have an Excel workbook with reference data - i.e. Days of the Year, Week#, Month# etc...

Other tabs bear data for certain processes, i.e. Sales etc.. which are date related.

I want to use a date slicer and roll up data so that when I look at Feb as a month, it gives me the total of all Sales and other departments etc...  The common theme is the date but I need to create Dax queries to compute things like revenue / expenses etc... which I can manage.

Please help.

Thanks,

S

1 ACCEPTED SOLUTION

Hi,

So I used the Append Query. I made sure my date fields in my data source were broken down for Day/Week/Month/Year for each tab in my Excel Spreadsheet. I then used the append query by making sure the date, i.e. week (as I wanted all data to be by week) was joined and replicated for all tabs so that the append query (mash up of all Excel tabs) was consistent. The Append Query then showed all fields across all tabs in my Excel sheet.  This then enabled me to add slicers for Week in my report. 

Thanks.

View solution in original post

7 REPLIES 7
v-huizhn-msft
Microsoft Employee
Microsoft Employee

Hi @SachinC,

In your Excel Workbool, there are different sheet including Date, Sales and so on? Based on your description, I am unable to reproduce your scenario, could you please share more details or sample data for  further analysis?

 

For appending and merging queries, please click the button highlighted in red line in following screenshot. More details, please review this article.

Capture1.PNG

 

Best Regards,
Angelia

hi,

heres more details. im using an excel workbook.

 

tab1

------

reference data, ie week dates 01/01/2017, 08/01/2017.....

week no: 1,2....

month#:1,2,3....

quarter:1,2...

year:2016,2017

 

tab2

-----

opportunity data, i.e,opportunity name

date: random dates

amount£

 

tab3

-----

project name

date

resource

 

i want to use the reference  data tab for my filters and roll up all other data so if i filter for week 13, as an example, it gives me a cumulative viewpoint as at week 13.

 

pls help ?

 

thank you

Hi @SachinC,

When you filter for week 13, you want a cumulative viewpoint including datas in tab1, tab2, tab3? In Power BI, please load all resource data in tab1, tab2, tab3 as three different tables. If there are raltionship between them, you can create a slicer including week to filter data, you only get the corresponding rows related to week 13.

While, in your given table, there is no commom field in three table. How to create relationship. If you want get all data at week 13 without relationship among them, we are unable to achieve. Thanks for understanding.

Best Regards,
Angelia

Yes, thanks for that. I figured this out eventually myself.

Hi @SachinC,

I am very glad to hear that you have resolve your issue, could you please share your solution which will help more people?

Best Regards,
Angelia

Hi,

So I used the Append Query. I made sure my date fields in my data source were broken down for Day/Week/Month/Year for each tab in my Excel Spreadsheet. I then used the append query by making sure the date, i.e. week (as I wanted all data to be by week) was joined and replicated for all tabs so that the append query (mash up of all Excel tabs) was consistent. The Append Query then showed all fields across all tabs in my Excel sheet.  This then enabled me to add slicers for Week in my report. 

Thanks.

Hi @SachinC,

Thanks for your detailed information. Please mark your solution as answer, so other people will find solution easily.

Best Regards,
Angelia

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.