import ics file

[nixonyx]

I would like to analyse calendar data, source is Outlook 2013. I have a series of ICS files that I export every day so I can monitor changes in bookings. Fortunately, this is only for a short period of time as there are 25 calendars to review and process.

I am familiar with the format of an ICS file but am stuck on how to get PBI to recognise the start (BEGIN:VEVENT) and end (END:VEVENT) of a calendar event so that each event is on a separate row and its elements (start/end date/time, location, organiser, attendees, subject, etc) are all in separate columns.

Hopefully this makes sense!

[nixonyx]

Thanks for the response. Unfortunately, it doesn't answer my question and I have removed the automatic flagging of it as the accepted solution.

Per my OP "stuck on how to get PBI to recognise the start (BEGIN:VEVENT) and end (END:VEVENT) of a calendar event so that each event is on a separate row". This means I want one row per event with all the salient event information in consecutive columns.

I can import the file, that's the easy part. I need PBI to recognise the start and end tags and convert the rows between these tags into columns.

I presume this is something I will just have to do in Excel. I was hoping to not have to write loads of macros to pre-process data. I thought the USP of PBI was that it would do everything in one product.

[alxdean]

I know this answer is two years over due, but here is some powerbi magic that can solve this issue. The trick is not to use the default Line.FromBinary and Table.FromColumn options but instead treat the input as one huge blob of text and then go to town on slicing it based on the Begin:VEVENT occurrence, then unpivot the data and finish cleaning it up. as I can't be sure in which order your columns are going to be transposed, I stopped at the split column step. feel free to continue cleaning up your columns from here. 

let
Source = Table.FromValue({Text.FromBinary(Web.Contents("https://url"))}),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Value", Splitter.SplitTextByDelimiter("BEGIN:VEVENT", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5", "Value.6", "Value.7", "Value.8", "Value.9", "Value.10", "Value.11", "Value.12", "Value.13", "Value.14", "Value.15"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Value.1"}, "Attribute", "Value"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter("#(cr)#(lf)", QuoteStyle.Csv), {"Value.1.1", "Value.2", "Value.3", "Value.4", "Value.5", "Value.6", "Value.7", "Value.8", "Value.9", "Value.10", "Value.11", "Value.12"})
in
#"Split Column by Delimiter1"

[v-caliao-msft]

@nixonyx

Which file format that you choose to import the ICS file in Power BI?

Currently there is no such an approach to import that file format. The available way from my side is: 

1. First convert the ICS file into CSV file format(you might need to use some ICS to CSV converter);
2. Then using Power BI Desktop to Import CSV files.

As you use Outlook 2013 as the data source here, we could directly save the calendar into .CSV file format: 

Open Outlook 2013, under: 

File->Open& Export->Import & Export->Export to a file->Comma separated values, after that, under your mailbox settings, choose to export Calendar.

Adding some reference: 

Data sources in Power BI Desktop 

Data sources for Power BI service

If any further assistance needed, please post back.

Regards,

Charlie Liao