Forum Discussion

SachinC's avatar
SachinC
Icon for Helper V rankHelper 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 ...
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.

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Helper V rankHelper 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
      Icon for Microsoft Employee rankMicrosoft 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

      • SachinC's avatar
        SachinC
        Icon for Helper V rankHelper V
        9 years ago

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