Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
naveen73
Helper III
Helper III

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.

 

naveen73_1-1694468659038.png

 

Thanks,

 

Naveen

1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4
wdx223_Daniel
Super User
Super User

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

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:

 

naveen73_0-1694548593660.png

 

= 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=11029...

 

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

Hi @wdx223_Daniel ,

 

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

 

Cheers,

 

Naveen

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors