DAX Append Query - Merging Data

[SachinC]

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

[v-huizhn-msft]

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.

Best Regards,
Angelia

[SachinC]

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

[v-huizhn-msft]

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

[SachinC]

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

[v-huizhn-msft]

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

[SachinC · 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.

[v-huizhn-msft]

Hi @SachinC,

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

Best Regards,
Angelia