split "header-value-header-value..." into columns

[gianleu]

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-IbBPCS-512-IvBPI-32-IbBPS-128;...

20200710-223708;OSCh-ON-BS-4k-CS-8k-IbPS-8k-IbLASUP-OFF-IbDASTD-OFF-IbBPCS-512-IvBPI-32-IbBPS-112:...

20200710-223708;OSCh-ON-BS-4k-CS-4k-IbPS-8k-IbLASUP-OFF-IbDASTD-OFF-IbBPCS-512-IvBPI-32-IbBPS-112;...

This is the result that I would like to get

timestamp	OSCh	BS	CS	IbPS	IbLASUP	IbDASTD	IbBPCS	IvBPI	IbBPS
20200710-212036	ON	4k	8k	8k			512	32	128
20200710-212036	ON	8k	8k	8k			512	32	128
20200710-223708	ON	4k	8k	8k	OFF	OFF	512	32	112
20200710-223708	ON	4k	4k	8k	OFF	OFF	512	32	112

I hope the example explains the situation better than I can do with words. Basically the second field in the csv contains a label that is always in the form "name1-value1-name2-value2-etc...". I would like to create a column for every distinct nameX, having nameX as header, and fill it with the corresponding valueX.

Is this feasible?

Any help is highly appreciated.

Best Regards

Gianluca 

[camargos88 · Accepted solution]

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"

[gianleu]

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"

[camargos88]

@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"

[Anonymous]

try to adapt this:

###edited####

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwNzTQNTI0MjA2s/YPds7Q9ffTdQrWNcnWdQ7WtcjW9UwKgNJOAUARU0MjXc8ypwBPXWMjsFiwrqGRhbWenl5MXkweHgMtKDTQyNjcwIKAC30cg0MDdP3d3IBsF8fgEBcoG6dFhkZWxFpkQqFFIB8pxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each [Column1] <> null and [Column1] <> ""),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    tab = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(tab,{"Column1.3"}),
    fields=List.Union(List.Transform(tab[Column1.2], each List.Alternate(Text.Split(_,"-"),1,1,1))),

    #"Added Custom" = Table.FromRecords(Table.AddColumn(#"Removed Columns", "Custom", each Record.FromList(List.Alternate(Text.Split([Column1.2],"-"),1,1,0),List.Alternate(Text.Split([Column1.2],"-"),1,1,1)))[Custom],fields,MissingField.UseNull)
in
    #"Added Custom"