Data from a Matrix that has dates both on rows and columns headers

[alecsonline]

Hi all, my source is an excel table that shows, on rows, the seats booked on a bus day by day. The columns headers are the "reading dates", the row headers are the "day of interest".

 

It means: on 05th april2018 (column) we had 7 seats booked for the run of the 3rd of june. 7 seats for the same run on  6th, 7th,..... then on 9th apr a new reservation so we had 8 seats booked....

Very easy to manage this on Excel, I am able to make an immediate"pick up" table that shows the running total per month, percentage of occupied seats, compare with previous year etc.

I need these data in Power Bi, as to see how the occupancy changes day by day. It is ok if I work on a single row (day), but I cannot find a solution to have the total occupied i.e. per month, or per week etc.

Any idea?

Thank you very much!

[danextian · Accepted solution]

Hi @alecsonline,

This involves unpivoting your table, transforming it from columnar to tabular. In Power Query/Query Editor,

• Promote first the appropriate row as headers if you haven't done so.
  • This should make the data view in Power Query should be very similar to your screenshot wherein the dates are the headers for columns two and above while the header for column 1 is blank.
• Select Days of Interest  Column and right-click on it.
• From the dialogue box, click Unpivot Other Columns
• Rename Attribute column to Reading Dates and Value to Seats Booked. 
• Change the data type of Days of Interest and Reading Dates columns to Date and Seats Booked to whole number.

Here's the complete code in M

let
    Source = Excel.Workbook(File.Contents("C:\Users\USER\Downloads\Seats pick up.xlsm"), null, true),
    NOTTI_Sheet = Source{[Item="NOTTI",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(NOTTI_Sheet, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Day of Interest"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Day of Interest"}, "Reading Date", "Seats Booked"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Seats Booked", Int64.Type}, {"Day of Interest", type date}, {"Reading Date", type date}})
in
    #"Changed Type"

[alecsonline]

GREAT! Thank you so much! Thank you all! Web communities with people like you all are the best representation of the positivity of the human race!!!

[ChandeepChhabra]

@alecsonline, Can you please share your excel file ?

[alecsonline]

Hi Chandeep I would like to share it, tried also when writing my question but I don't know how to do it. Can you tell me how can I share the excel file here in tthe forum?

Tks!

[ChandeepChhabra]

@alecsonline Please put a link to download the file from dropbox / google drive / one drive

[alecsonline]

Hi thank you, here is the link to the dropbox file

https://www.dropbox.com/s/ogwuh1yz1btrwbb/Seats%20pick%20up.xlsm?dl=0

Please ignore any "error" notification on the file since I cut it to make it lighter.

The Sheet "Notti" represents the data I would like to manage with Power BI.

The shett "Pick up" represent the results that I need.

As you can see with excel it is very easy to insert the "Month total", but I would like to analize the data in a more effecitve way using Power Bi, i.e. focusing not only to the entire month but, for example, to weeks, or holidays week ends and so on.

Or creating graphs.

That kind of flexibility that Excel cannot provide.

Unfortunately I can only receive the datas in that specific format. 

Thank you very much for your interest!

Greetings, Alessandro.