Create same line chart that is already in excel

[Grizzlydad]

Hi all

I am trying to create the same line chart that is pictured in Power Bi desktop, using the line chart visual. I can't get it to exactly replicate.  You can see it works as expected in excel.

I basically want a 3 week comparison of data flow, capturing each day of the week so that the week commencing line has a different colour.

There should be 3 line colours, one representing each week commencing and then the X axis should display the days of the week.

Any thoughts how I can achieve this, I need to know what to put in the relevant axis, legend and value fields?

[P_D_G · Accepted solution]

I think you have clicked on the "Unpivot Columns" instead of "Unpivot Other Columns" (see picture).

This is the output you'll see:

Hope it helps,

PDG

[P_D_G]

Hi @Grizzlydad ,

Go to Power Query, select the WeekCommencing column > Transform > Unpivot Other columns.

You'll end up with 3 columns: 

- Week Commencing

- Attribute = name of the days

- Value

If you close &Apply you can use the following setting to get your desired result.

(the chart is showing dummy data)

Hope it helps,

PDG

[Grizzlydad]

Hiya I did that but got 2 new columns in edit query, like so, and all the other columns remained.  I sthere a difference between Power Query and Edit query?

[P_D_G · Accepted solution]

I think you have clicked on the "Unpivot Columns" instead of "Unpivot Other Columns" (see picture).

This is the output you'll see:

Hope it helps,

PDG

[Grizzlydad]

Yes this has worked, thank you.  However can I order the axis to have the days in the correct order so Monday to Sunday or similar, at the moment it is a bit random showing Friday, Monday, Saturday, Sunday, Thursday, Tuesday and Wednesday?

Thnaks again

[P_D_G]

Custom sort order of values:
https://radacad.com/sort-a-column-with-a-custom-order-in-power-bi

NB: You need to use the column fromt the 2nd column (only containing the days &custom sort order) in your visual!

Best,

PDG

[Grizzlydad · Accepted solution]

Perfect, thanks for all your help!