Forum Discussion

SachinC's avatar
SachinC
Helper V
9 years ago
Solved

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

Need Help
  • SachinC's avatar
    SachinC
    9 years ago

    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.

7 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
      SachinC
      Helper V
      9 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        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