Forum Discussion

SusuYes's avatar
SusuYes
Helper III
3 years ago
Solved

Transpose Repeating Header Rows into Column Headers after Merging

I have an ICS calendar that I am trying to transform in PowerBI. However, the data I recieve is organised as follows:

Data
Event: NAME XXX
Date: xx xx xx
Location: NOWHERE
URL: www.hi.com
Event: NAME YYY
Date: yy yy yy
Location: SOMEHWERE
URL: www.hello.com

... and so on

I need to be able promote the text before the : delimiter on every four rows as headers. so that my data looks like this:

Event Date Location URL
NAME Xxx xx xxSOMEHWEREhello.com
NAME Yxx xx xxNOWHEREbye.com

I can use SQL or Python or data visualisation software such as PowerBI, alternatively, good ol' Excel works fine.

I tried other tools and workarounds such as uploading the ICS calendar into my Outlook calendar and then exporting the calendar. This worked fine but it is a work around.

 

These are the steps I followed so far: Most of the steps are data cleaning excepting the grouping at the end. 

however, I cannot properly expand the tables without causing a mess with the data. 

 

Any helps would be appreciated. The data can be found here: shorturl.at/ektHK 

 

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci1LzSuxUvBz9HVViIiIUIrViVZySSxJtVKoqIAgsJBPfnJiSWZ+HlClf7iHa5ArWDQ0yMdKoby8XC8jUy85PxcshmxgZGQkkoGVlRCEZmCwv6+rRzimkak5OfkQU2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Custom1 = Table.FromRecords(Table.Group(Source,"Column1",{"n",each Record.FromTable(#table({"Name","Value"},List.Transform([Column1],each Splitter.SplitTextByDelimiter(": ")(_))))},0,(x,y)=>Byte.From(Text.StartsWith(y,"Event")))[n])
in
    Custom1

     

1 Reply

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci1LzSuxUvBz9HVViIiIUIrViVZySSxJtVKoqIAgsJBPfnJiSWZ+HlClf7iHa5ArWDQ0yMdKoby8XC8jUy85PxcshmxgZGQkkoGVlRCEZmCwv6+rRzimkak5OfkQU2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Custom1 = Table.FromRecords(Table.Group(Source,"Column1",{"n",each Record.FromTable(#table({"Name","Value"},List.Transform([Column1],each Splitter.SplitTextByDelimiter(": ")(_))))},0,(x,y)=>Byte.From(Text.StartsWith(y,"Event")))[n])
in
    Custom1