Forum Discussion
Gathering Field data from different table of same header fields
Hi Team,
new to BI word, would liek to get some help on below item,
I have two tables with same field names,
need to add Table A filed information into Table B as per dates listed in table - could you please help how to manage
appreciate your support
Thanks
V
Table A
| Line | AREA | Code | 18-Jul-25 | 25-Jul-25 | 2-Aug-25 |
| 1 | CD | 99AP | 125 | 131 | 101 |
| 2 | QAB | 99AP | 18 | 21 | 43 |
| 3 | QAS | 99AP | 2 | 1 | 0 |
| 4 | QAC | 99AP | 5 | 3 |
|
Table B
| Line | AREA | Code | 18-Jul-25 | 25-Jul-25 | 2-Aug-25 |
| 24 | CD | 99AP | Expected 125 | ||
| 25 | QAB | 99AP | |||
| 26 | QAS | 99AP | Expected 0 | ||
| 27 | QAC | 99AP |
Hi Vemul ,
Based on my uderstanding i writing the below options,
1. you can use Reference option, if you would like to get replicate or get all the data from Table A to Table B and the Table B should be automatically updated whenever Table A gets updated,
2. When you have different in Table B and would like to pull data Table A as additional columns then you have to Merge Table A with Table B based on a common column between the two tables.
Please share the expected output in detail if you need to drive any particular result after Merging two tables.
9 Replies
Hi,
Do this in Power Query
- Using "Unpivot Other Columns", transform each table into a 5 columnar one - the last 2 columns being attribute and value
- Merge Table A into Table B
- Pivot back Table B
HI Aburar,
Table A is manual entry and Table B is system driven, it need Table B capture data from Table A when ever it updates baed on date
hope this helps
V
Hi Vemul ,
You can solve this cleanly in Power Query with a few steps:
-
Unpivot your tables: In Power Query, use “Unpivot Other Columns” on both Table A and Table B, so all your date columns become attribute/value pairs (i.e., you’ll have columns like Line, AREACode, Date, Value).
-
Merge the tables: Merge Table B with Table A on the relevant keys (Line, AREACode, Date). This will bring in the matching values from Table A to Table B for each date.
-
Expand and pivot if needed: Expand the merged columns to bring in the values from Table A. If you want your final output in the original wide format, just pivot back by date.
This method ensures that Table B will always pick up the latest info from Table A for the matching dates and headers no manual editing needed.
If you only need to bring in one specific field and not several, you can also use DAX’s LOOKUPVALUE in a calculated column, but Power Query is much more flexible and scalable for what you described.
Could you please clarify how your solution differs from the one already shared by Ashish_Mathur ?
-
Hi Vemul ,
Based on my uderstanding i writing the below options,
1. you can use Reference option, if you would like to get replicate or get all the data from Table A to Table B and the Table B should be automatically updated whenever Table A gets updated,
2. When you have different in Table B and would like to pull data Table A as additional columns then you have to Merge Table A with Table B based on a common column between the two tables.
Please share the expected output in detail if you need to drive any particular result after Merging two tables.
