Forum Discussion

naveen73's avatar
naveen73
Helper III
2 years ago
Solved

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...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago

    = 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])

naveen73's avatar
naveen73
Helper III
2 years ago

Hi wdx223_Daniel 

 

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=true

 

Can you let me know if the link works.

 

Thanks so much,

 

Naveen

 

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
2 years ago

= 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])

  • naveen73's avatar
    naveen73
    Helper III
    2 years ago

    Hi wdx223_Daniel ,

     

    Thanks so much for your efforts. This is working for me.

     

    Cheers,

     

    Naveen