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 than the start date but less than the end date of the month it produces the word current or previous. When I merge the tables using the end date for my merge it is showing previous on all the entries that should show current.

 

I have checked the table it is merging from and that is showing correct. Can anyone give me any guidence, I am very new to Power BI and using power query to manipulate data.

 

I do have an excel version of what I am trying to create in power bi but as you can imagine it uses a lot of formulas and runs very slow. Its also a lot less interactive than what I think a power bi verson would achieve.

 

Any help for a Power BI/Power Query newby will be greatfully received

 

Thanks

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.

13 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    5 years ago

    I am sure we can help.

    It sounds like the Merge is not set up correctly.

    Can you show me some sample data and a picture of the Merge screen please?

    • Krisso1975's avatar
      Krisso1975
      Regular Visitor
      5 years ago

      Hi HotChilli

       

      Thank you for the help

       

      This is the screen I have put the formula in to show current period

       

      And the other table merges on Month End Date but is showing previous still even though the above is showing current

       

      Thanks for your help

      • HotChilli's avatar
        HotChilli
        Community Champion
        5 years ago

        The first thing which is odd is that the datatype on the first Current Period screen is set to 'any', it should be text.

        I would like to see the screen from the Merge step - the one where you pick the 2 tables and the join types please.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    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.