Forum Discussion

nixonyx's avatar
nixonyx
Helper I
10 years ago

import ics file

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 ...
nixonyx's avatar
nixonyx
Helper I
10 years ago

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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"