Forum Discussion

josephmwood's avatar
josephmwood
Regular Visitor
5 years ago

Comparing years in line graph

I saw a previous message that was similar to this, but I could not figure out how to get the answer to work at all.   I have a dashboard I have made in Excel using a pivot chart from a pivot table....
Need Help
KNP's avatar
KNP
Super User
5 years ago

Thanks for that.

With a couple of steps, that data, looks like this...

 

 

The important part, add a new column for 'year' (for your legend), and add a new column for your X axis, I did week of year but you can adapt to your needs.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7NS0ms1FHwTaxUMDTTUTAyMDJU0lFyzs9NysxLTVEIyS9JzAEKOObkKASnFpVlJqcWQ7lhqXmlqcVwFYbGxmZKsTqoRlpSZqKFIYaJhuZgIw3IM9LSANNAA0oMNDQwwTQR7GtDS/JMNDK1MMY00ogiI81MgUbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Venue Type" = _t, Service = _t, Venue = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Venue Type", type text}, {"Service", type text}, {"Venue", type text}, {"Total", Int64.Type}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Year", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type)
in
    #"Inserted Week of Year"

 

Hope this helps.

 

Regards,

Kim

josephmwood's avatar
josephmwood
Regular Visitor
5 years ago

I appreciate the quick response Kim!

Unfortunately I'm a complete noob to PowerBI and that code makes zero sense to me and I would have absolutely no idea how to implement it in my PowerBI dashboard that I have connected to my SQL server. Is there anyway you could explain it a bit clearer to me? I'm not in a hurry at all. Thanks so much!

  • KNP's avatar
    KNP
    Super User
    5 years ago

    I understand. To use my sample code follow the below steps.

     

     

    Go to Transform data.

     

     

     

     

     

     

     

     

    Right click in a blank area in the query pane and select New Query --> Blank Query

     

    With the query selected, choose Advanced Editor

     

     

    Paste my code over what is there and click done.

     

    You'll then be able to see the steps I followed.

     

    Essentially, all you're trying to do is add a column for year for the legend and one for a date part of some kind that excludes year (I used week number) for the X axis.

     

    What you end up with is...

     

     

    • josephmwood's avatar
      josephmwood
      Regular Visitor
      5 years ago

      Thanks so much! 

       

      I did this on my table and it created the new queries and i see them, but they have no effect on my table right now. I tried creating relationships between the date and my tables, but it doesn't do anything. Do I have to customize your code to fit with my tables and correct column names that I'm pulling from my server? I'm using several different sections of a server to bring information together.

      • KNP's avatar
        KNP
        Super User
        5 years ago

        I only included that query to serve as an example of how I'd approach it so you could see the individual steps. Sorry, I may not have explained that well. 

         

        What you need is a 'year' (in your date dimension if you have one) for the legend and something for your X axis like week number. 

        Are you able to attach a screen shot of your model showing the joins? I might be able to offer better advice if I can see the data model.