Forum Discussion
Solved
split "header-value-header-value..." into columns
Hi all, I have a csv file that looks like this: timestamp;test;... 20200710-212036;OSCh-ON-BS-4k-CS-8k-IbPS-8k-IbBPCS-512-IvBPI-32-IbBPS-128;... 20200710-212036;OSCh-ON-BS-8k-CS-8k-IbPS-8k-...
Hi gianleu ,
Try this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwNzTQNTI0MjA2s/YPds7Q9ffTdQrWNcnWdQ7WtcjW9UwKgNJOAUARU0MjXc8ypwBPXWMjsFiwrqGRhbVSrA5e4ywoMs7I2NzAgoDrfByDQwN0/d3cgGwXx+AQFygbpzWGRkRaY0K5NbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"timestamp;test;" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"timestamp;test;", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "timestamp;test;", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"timestamp;test;.1", "timestamp;test;.2", "timestamp;test;.3"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"timestamp;test;.1", "date"}, {"timestamp;test;.2", "values"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"timestamp;test;.3"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each let _list = List.Split(Text.Split([values], "-"), 2) in Table.PromoteHeaders(Table.Transpose(Table.FromList(List.Transform(_list, each Text.Combine(_, ":")), Splitter.SplitTextByDelimiter(":"))))), #"Expanded Custom" = Table.ExpandTableColumn( #"Added Custom", "Custom", //{"OSCh", "BS", "CS", "IbPS", "IbBPCS", "IvBPI", "IbBPS", "IbLASUP", "IbDASTD"} List.Union(List.Transform(#"Added Custom"[Custom], each Table.ColumnNames(_))) // Dynamic headers, if necessary ), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"values"}) in #"Removed Columns1"
Hi gianleu ,
Try this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwNzTQNTI0MjA2s/YPds7Q9ffTdQrWNcnWdQ7WtcjW9UwKgNJOAUARU0MjXc8ypwBPXWMjsFiwrqGRhbVSrA5e4ywoMs7I2NzAgoDrfByDQwN0/d3cgGwXx+AQFygbpzWGRkRaY0K5NbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"timestamp;test;" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"timestamp;test;", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "timestamp;test;", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"timestamp;test;.1", "timestamp;test;.2", "timestamp;test;.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"timestamp;test;.1", "date"}, {"timestamp;test;.2", "values"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"timestamp;test;.3"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each let _list = List.Split(Text.Split([values], "-"), 2) in Table.PromoteHeaders(Table.Transpose(Table.FromList(List.Transform(_list, each Text.Combine(_, ":")), Splitter.SplitTextByDelimiter(":"))))),
#"Expanded Custom" =
Table.ExpandTableColumn(
#"Added Custom",
"Custom",
//{"OSCh", "BS", "CS", "IbPS", "IbBPCS", "IvBPI", "IbBPS", "IbLASUP", "IbDASTD"}
List.Union(List.Transform(#"Added Custom"[Custom], each Table.ColumnNames(_))) // Dynamic headers, if necessary
),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"values"})
in
#"Removed Columns1"
Hi camargos88 ,
I changed the source and it works.
Thx
Just out of curiosity. Can't I loop into the values separated by "-" and create columns based on value position, so that the solution would be more dynamic?
Thx again
Gianluca
let
Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\user\Downloads\TEST.txt"), null, null, 1252)}),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"timestamp;test;", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "timestamp;test;", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"timestamp;test;.1", "timestamp;test;.2", "timestamp;test;.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"timestamp;test;.1", "date"}, {"timestamp;test;.2", "values"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"timestamp;test;.3"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each let _list = List.Split(Text.Split([values], "-"), 2) in
Table.PromoteHeaders(Table.Transpose(Table.FromList(List.Transform(_list, each Text.Combine(_, ":")), Splitter.SplitTextByDelimiter(":"))))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"OSCh", "BS", "CS", "IbPS", "IbBPCS", "IvBPI", "IbBPS", "IbLASUP", "IbDASTD"}, {"OSCh", "BS", "CS", "IbPS", "IbBPCS", "IvBPI", "IbBPS", "IbLASUP", "IbDASTD"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"values"})
in
#"Removed Columns1"
gianleu ,
This part:
List.Split(Text.Split([values], "-"), 2)is similar to a loop, it creates pairs for the values.
Also, I've edited the code to get dynamics headers:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwNzTQNTI0MjA2s/YPds7Q9ffTdQrWNcnWdQ7WtcjW9UwKgNJOAUARU0MjXc8ypwBPXWMjsFiwrqGRhbVSrA5e4ywoMs7I2NzAgoDrfByDQwN0/d3cgGwXx+AQFygbpzWGRkRaY0K5NbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"timestamp;test;" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"timestamp;test;", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "timestamp;test;", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"timestamp;test;.1", "timestamp;test;.2", "timestamp;test;.3"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"timestamp;test;.1", "date"}, {"timestamp;test;.2", "values"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"timestamp;test;.3"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each let _list = List.Split(Text.Split([values], "-"), 2) in Table.PromoteHeaders(Table.Transpose(Table.FromList(List.Transform(_list, each Text.Combine(_, ":")), Splitter.SplitTextByDelimiter(":"))))), #"Expanded Custom" = Table.ExpandTableColumn( #"Added Custom", "Custom", //{"OSCh", "BS", "CS", "IbPS", "IbBPCS", "IvBPI", "IbBPS", "IbLASUP", "IbDASTD"} List.Union(List.Transform(#"Added Custom"[Custom], each Table.ColumnNames(_))) // Dynamic headers, if necessary ), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"values"}) in #"Removed Columns1"