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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdata
Frequent Visitor

Combining Data and Dates from two different tables

Hi,

I am looking to combine values and dates that are in two seperate tables.

 

In the first table, the rows contains time series data (fi data) for different companies (identifier in first column).  The second table is structured the same (identifiers in first column), except the cells contains dates that correspond to the data in the first table. 

 

Is there a way to combine these data so that I end up with a single table that has 3 colums as follows: 

 

Column 1: company identifier

Column 2: date

Column 3: Fi data

 

Thanks!

 

Jonathan

3 ACCEPTED SOLUTIONS

you might try,

 

in query editor to un-pivot both tables

 

then merge tables

 

in a database one can't do that but here in Power BI the simple ordering of the rows may allow that to work

www.CahabaData.com

View solution in original post

Assuming that your tables' layout will always match at a cell level (as you show it in your dummy data), I would unpivot both tables, add an index column, then merge on the index column.  This should get all the values for each date in the appropriate rows.

View solution in original post

@jdata  assuming your data does match as @dkay84_PowerBI says

all you really need is the Index in each table - duh! Smiley Wink

 

Query Editor - Unpivot, Index and Merge.gif

View solution in original post

11 REPLIES 11
Eric_Zhang
Employee
Employee


@jdata wrote:

Hi,

I am looking to combine values and dates that are in two seperate tables.

 

In the first table, the rows contains time series data (fi data) for different companies (identifier in first column).  The second table is structured the same (identifiers in first column), except the cells contains dates that correspond to the data in the first table. 

 

Is there a way to combine these data so that I end up with a single table that has 3 colums as follows: 

 

Column 1: company identifier

Column 2: date

Column 3: Fi data

 

Jonathan


@jdata

Roughly I think you can use the UNION to do the combination. As there's no date in Table2, what would you like to show for the column date in the combined table for the data coming from table2?

Table =
UNION (
    SELECTCOLUMNS (
        Table1,
        "company", Table1[company],
        "date", Table1[date],
        "fi date", Table1[fi date]
    ),
    SELECTCOLUMNS (
        Table2,
        "company", Table2[company],
        "date", "",
        "fi date", Table2[fi date]
    )
)

Hi v-lvzhan-msft,

 

Thank you for the response.  I'm not sure we are on the same page, based on this question below:

 

"As there's no date in Table2, what would you like to show for the column date in the combined table for the data coming from table2?"

 

Table 1

Company A      10     12    14   18  

Company B       1        3      5     9  

Company C       8       16     5     8

 

 

Table 2

Company A      1/1/2015     12    14   18  

Company B       1        3      5     9  

Company C       8       16     5     8

jdata
Frequent Visitor

Woops that just posted without me finishing. Here are conceptual examples of the two tables and the table I'd like to convert the data to. 

 

 

 

Table 1

Company A      10     12    14   18  

Company B       1        3      5     9  

Company C       8       16     5     8

 

 

Table 2

Company A      1/1/2015     1/2/2015     1/3/2015   1/4/2015

Company B       1/3/2015     1/4/2015     1/5/2015   1/6/2015

Company C       12/30/2014  12/31/2015  1/1/2016   1/2/2016

 

Question: how do I convert these tables to this:

 

Company       Date          Data

Company A    1/1/2015     10

Company A    1/2/2015     12

Company A    1/3/2015     14

Company A    1/4/2015     18

Company B    1/3/2015       1

Company B    1/4/2015       3

Company B    1/5/2015       5

Company B    1/6/2015       9

Company C    12/30/2014   8

Company C    12/31/2014   16

Company C    1/1/2014       5

Company C    1/2/2014       8

Assuming that your tables' layout will always match at a cell level (as you show it in your dummy data), I would unpivot both tables, add an index column, then merge on the index column.  This should get all the values for each date in the appropriate rows.

@jdata  assuming your data does match as @dkay84_PowerBI says

all you really need is the Index in each table - duh! Smiley Wink

 

Query Editor - Unpivot, Index and Merge.gif

jdata
Frequent Visitor

Thanks guys ! This is all very helpful!  Smiley Very Happy

you might try,

 

in query editor to un-pivot both tables

 

then merge tables

 

in a database one can't do that but here in Power BI the simple ordering of the rows may allow that to work

www.CahabaData.com
Sean
Community Champion
Community Champion

@MarcelBeug  In the mood for a video? Smiley Happy

MarcelBeug
Community Champion
Community Champion

@Sean Yes, but this time it will be: watching a detective together with my wife, starting 10 minutes from now at 8:40 PM CET.

Specializing in Power Query Formula Language (M)
Sean
Community Champion
Community Champion

@MarcelBeug  Smiley LOL Okay have fun! Smiley Very Happy

jdata
Frequent Visitor

Sure!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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