Forum Discussion
Helper IData from a Matrix that has dates both on rows and columns headers
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!
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"- Promote first the appropriate row as headers if you haven't done so.
6 Replies
- ChandeepChhabra
Impactful Individual
alecsonline, Can you please share your excel file ?
alecsonlineHi 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
- danextian
Super User
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"- alecsonlineGREAT! 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!!!
- Promote first the appropriate row as headers if you haven't done so.
