Forum Discussion

Krisso1975's avatar
Krisso1975
Regular Visitor
5 years ago
Solved

Merge not pulling through correct information

I have couple of tables in power query, 1 for periods in the year and one showing billing data. I have created a column in the periods in the year that seems to be working that if today is greater th...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Krisso1975 

     

    Based on your description, I assume that you want to create a custom column based on date column. I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a custom column with the following m codes.

    let 
start = Date.AddDays( Date.EndOfMonth( Date.AddMonths([Date],-1)),1),
end = Date.EndOfMonth([Date]),
today = Date.From(DateTime.LocalNow())
in 
if today<start
then "Next"
else if today>end 
then "Previous"
else if today>=start and today<=end 
then "Current"
else null

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

HotChilli's avatar
HotChilli
Community Champion
5 years ago

So that would explain it. The field doesn't come through in the merged table because there is no matching field in the splitBills table.  Does that make sense?

 

Krisso1975's avatar
Krisso1975
Regular Visitor
5 years ago

🙈How did I not see that. The current should be the line above (01/03/2021 - 31/03/2021) Our system automatically creates a period 13 each year and this turns into period 1 when the new year is opened. So my question is now how do I fix the formula above to show current in the right place. I have just tried a few variations and none of those work