Using two Datasets in power BI

[udayadeven]

Hi Experts ,

I have two datasets having same fields from two lists .. first list having current month data and the second one is having historical data ..

How can we switch the datasets in the same powerbi Report . In brief , i need to have a check box(or any otherway) to select the current or history dataset and change the report figures accordingly

pls your advice on this

rgds

AD 

[timahenning2]

Ideally, your dataset transformations should be done before you are working in your Power BI model.  Transform data as close to the data source as possible.
Roche’s Maxim of Data Transformation – BI Polar (ssbipolar.com)

Your original statement: "I have two datasets having same fields from two lists .. first list having current month data and the second one is having historical data "

If you are working with files, then you should use the Append functionality in Power Query to combine your datasets into one.

Working with a database as your data source?  Create a database view to transform the data into one dataset (UNION) and then import into Power Query/Power BI.

Union requires the fields to be the "same" interface.  You can find the definiton of "same" in the DB service help files and examples.  It makes more sense when you see the examples.

Tim

[udayadeven]

Hi timahenning ,

thanks for the advice .. I am using share Point Lists for this ..

rgds

AD

[timahenning2]

If I have provided a solution, would you mind a selecting the thumbs up?

Thanks.

[timahenning2 · Accepted solution]

AD, the concept is the same for SharePoint Lists.

1. Open Power Query and select the current SharePoint List table.  Choose Append Queries As New from the PQ menu bar.

2. Select the tables like this to union the data in Power Query:

3. Your Power BI model should look something like this:

4. You can now view the current and historical data in your visualization by changing the way your filter the data.

 Tim

 

[udayadeven]

Thanks timahenning2  for your advice ..  I did it as you said .. its working as expected 

rgds

AD

[timahenning2]

Have you thought about combining (union) the datasets into one table?  Your report and measures would point to one table.  Using a slicer, filtered by a field like date, you could then display current or historical data.   

[udayadeven]

Hi Timahenning ,

Thanks for your advice ,.. yes , i am trying to use the UNION . but I a quick concern on this .. In my first dataset having new columns and Messures created thro PowerBI , Do i need to have the same things in second Dataset .. I tried to create but cannot use the same Messure name .. 

pls advice ..

rgds

AD