Forum Discussion
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
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
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! :smileywink:
11 Replies
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
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
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
