Forum Discussion
mapping dates value
Hi,
I have two data sets that look a bit like Company A and Company A. Company A only has quarterly data while b has monthly data.
I want to map the value of Company A to the correct month and also to the next two months until the month it has value again. Please see screenshot what I am trying to achieve in column G.
Thanks,
Naveen
= Table.Combine(Table.Group(#"Company B","Date",{"n",each Table.AddColumn(_,"Valuex",(x)=>#"Company A"{[Date=[Date]{0}]}?[Valuex]?)},0,(x,y)=>Byte.From(#"Company A"{[Date=y]}? is record))[n])
4 Replies
= Table.Combine(Table.Group(CompanyB,"Date",{"n",each Table.AddColumn(_,"x",(x)=>CompanyA{[Date=[Date]{0}]}?[Value]?)},0,(x,y)=>Byte.From(CompanyA{[Date=y]}? is record))[n])
Thanks so much for responding. I tried your solution but not sure why it is not working. Please see the screenshot the two separate table and the end result:
= Table.Combine(Table.Group(#"Company B","Date",{"n",each Table.AddColumn(_,"x",(x)=>#"Company A"{[Date=[Date]{0}]}?[Value]?)},0,(x,y)=>Byte.From(#"Company A"{[Date=y]}? is record))[n])
Please see attached the Excel sheet on my Google Drive:
https://docs.google.com/spreadsheets/d/1YMOSqw3_mawgGxKalOKWRomEdQr81sbK/edit?usp=sharing&ouid=110294040238289903125&rtpof=true&sd=trueCan you let me know if the link works.
Thanks so much,
Naveen
= Table.Combine(Table.Group(#"Company B","Date",{"n",each Table.AddColumn(_,"Valuex",(x)=>#"Company A"{[Date=[Date]{0}]}?[Valuex]?)},0,(x,y)=>Byte.From(#"Company A"{[Date=y]}? is record))[n])
