Forum Discussion
[ Joining 2 Tables with specif layout ]
the solution maybe only work for this scenario. If we have more dates, e.g. 202402, then will not work. Let's see if anyone else can help you on this.
Super UserAnonymous
pls see the attachment below below
however, the question is how to get the data highlighted in yellow? could you pls elaborate more ?
ryan_mayu thks for the pbix file!!
Its almost there, but i cant loose the data in yellow in the merge/append process. I need the full outter join (which you did), but i cant loose the [month data] / [vision] data in the process. Those 2 columns need to be repeated.
[month data] contains near 700 [month bill acc] and every time i have a context existent in table 2 and no existent in table 1, i need to add that row for that [month bill acc] in table 1. The original table 1, have some years of [month data], each of them have repeated [month bill acc], and my difficult here is the sometimes table 2, has data not contained in table 1.
- ryan_mayu
pls provide the logic of getting the data. you must have different months in your real data. Then how can we know to display 202401 or 202402 or other month?
Hi ryan, i tried to make a better example. (i cant publish the excel right now because im working and it will be blocked).
The vision column (days) are a count related to [month bill acc] minus [month data], and if the difference is zero then 30d, if it is 1, then 60d, if its 2 months then 90d, and so on. To do that, we use a sequential yearmonth formula (year * 12 + month - 1).
[month data] will never be greater thand [month bill acc] in this model, but it can be lower.
- ryan_mayu
Anonymous
14770 is from locaion b and type n . how can we link that to location g and type n?
pls also paste the table here like below
month datamonth bill acclocationversiontypetp clientcityvalor billing
202401 202401 c 30d n high no info 322 202401 202401 d 30d n high no info 336 202401 202401 a 30d n index no info 210 202401 202401 b 30d n low no info 311 202401 202401 b 30d n low no info 111 202311 202401 c 90d f high no info 49 202311 202401 d 90d f high no info 46 202311 202401 a 90d f low no info 32 202311 202401 b 90d n low no info 14 202311 202401 b 90d n low no info 555 202311 202401 xa 90d n high no info 26 202311 202401 xb 90d n index no info 646 202311 202401 e 90d n index no info 28 202311 202401 f 90d f high no info 22 202311 202401 g 90d g index no info 23 that will be easier for us to import data.