Forum Discussion

SusuYes's avatar
SusuYes
Icon for Helper III rankHelper 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.c...
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

     

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity 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