Extracting data from string - mixed column names and values

[jacek_k]

Hi Guys,

I have to extract data from strings. 100 columns and 10k+ rows.

Every row contains column names and corresponded values after semicolon but not each row contains each column.

I found solution but I think it is to complicated and can generate errors because of simply use of isOdd/isEven/FillDown functions. For example I had to add Tabel.Buffer to handle errors.

Is there easiest way to get the result?

Link to sample file on my onedrive:

Sample_file 

Regards

Jacek

[jacek_k]

Hi, it works perfectly, i forgot to reply 😞

[AlexisOlson · Accepted solution]

Maybe this will work better. I'm not sure it will, but it's worth a try.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"To Tables" = Table.TransformColumns(#"Added Index",
        {{"Data", each
            let
                Pairs = List.Split(Text.Split(_, ";"), 2),
                ColNames = List.Transform(Pairs, each _{1}),
                ColValues = List.Transform(Pairs, each _{0})
            in
                Record.ToTable(Record.FromList(ColNames, ColValues))
            , type table}}
        ),
    #"Expanded Data" = Table.ExpandTableColumn(#"To Tables", "Data", {"Name", "Value"}, {"Name", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Data", List.Distinct(#"Expanded Data"[Name]), "Name", "Value")
in
    #"Pivoted Column"

The idea here is that the ColName;ColRowValue pairs are similar to how a record is structured [ColName=ColRowValue]. I split each text string into a list and then use List.Split to group it into pairs, taking the names from the first element of each pair and the values from the second element of each pair. Then turn that pair of lists into a record and then a table. Expand the tables and pivot on the names.

There may be some possible buffering optimizations but I'll let you experiment with that.