Transpose/unpivot certain columns - displaying as chart

[po]

Hi,

Have data of format below

Customer,YrWk,Mon_Delivery_BreadFlag, Tuesday Delivery_Bread_Flag.... Sunday Delivery BreadFlag,Nonday_Delivery_Milk_Flag, Tuesday_Delivery_Milk_Flag.., Sunday Delivery Milk Flag i..e all on one row for each customer/week.

i.e delivery flag for each day of week for delivery of 2 products bread and milk

example data

10,201901,Y,N,Y,N,Y......

20,201901,Y,N,N,N...

Wish to display this data in a bar chart to show the number of Customers who got bread 

One chart focus on bread with day along x-axis and count of number of  customers delivered on y-axis.

Similar separate chart for milk.

Can create for each day of week e.g number of customers delivered bread Monday

                                                           number of customers delivered bread Tuesday

...

                                                           number of customers delivered milk Monday

                                                           number of customers delivered milk Tuesday

with these counting the rows where set to Y in the relevant column and then creating calculated table to show worts days for deliveries i.e sorting by lowset value.

However, wondering if better approach to achieve this?

Thanks

[CheenuSing]

Hi @po,

Have you tired unpivoting the MonBreadFlag, TueBreadFlag, etc ..

Then create a bar chart with filters set for all BreadFlags and another chart with filters set for MilkFlag and Values as Count of Customers.

Let me know if this works for you.

Cheers

CheenuSing

[po]

Hi All,

Thanks for replies.

This works great.

Just a couple of queries.

The x-axis has count in decimal format  e.g 7.00 rather than 7  - thought we woudl be abel to change this easily on format x-axis but doesn't seem to be an option to make whole number - our store number is defined a stext as occassionalyy they put 123/124 characters within it.

Secondly if trying to sort on the sortde column in matrix without actaully including the sort column i.e. 1 to 7 doesn't look to apply sort despite fact sort the default at modeeliong level.

Any thoughts on how we can remove the decimal format on the x-axis and ensure a matrix always sorts by a speicfic column?

Thanks

[po]

Hi,

 Fixed the x-axis to display units none.

Just the sort to fix.  Any thoughts?

Thanks

[po]

Managed to fix by ensuirng used conditional column as per you're .pbix rather than calculated column for sort.

[MFelix · Accepted solution]

Hi @po,

Believe that the best option is on the query editor select the columns of the weekdays flags and unpivoted them you will get a table with the followong format:

Customer YearWk     Attribute                Flag

10	201901	Monday_Bread	Y
10	201901	Tuesday_Bread	N
10	201901	Wednesday_Bread	Y
10	201901	Thursday_Bread	N
10	201901	Friday_Bread	Y
10	201901	Saturday_Bread	N
10	201901	Sunday_Bread	Y
10	201901	Monday_Milk	Y
10	201901	Tuesday_Milk	N
10	201901	Wednesday_Milk	Y
10	201901	Thursday_Milk	N
10	201901	Friday_Milk	Y
10	201901	Saturday_Milk	N
10	201901	Sunday_Milk	Y
20	201901	Monday_Bread	Y
20	201901	Tuesday_Bread	N
20	201901	Wednesday_Bread	N
20	201901	Thursday_Bread	Y
20	201901	Friday_Bread	Y
20	201901	Saturday_Bread	N
20	201901	Sunday_Bread	N
20	201901	Monday_Milk	Y
20	201901	Tuesday_Milk	Y
20	201901	Wednesday_Milk	Y
20	201901	Thursday_Milk	N
20	201901	Friday_Milk	N
20	201901	Saturday_Milk	N
20	201901	Sunday_Milk	Y

You can then proceed to do more changes as Split row to columns to get the product and the Day of the week, add a number per week day to sort the weekday and then use it on your charts.

Check PBIX file attach with all the transformations I refer and a sample chart.

Regards,

MFelix