Forum Discussion

dp3450's avatar
dp3450
Copper Contributor
Apr 03, 2021

Create custom columns in excel

I have an excel file with a column that says customfields. Data in this column are for multiple fields. I want to create a column for the each custom value in the excel file. Data in the custom col...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 04, 2021

dp3450 

As variant that could be done with Power Query

If source texts (in this case B2:B4) name as Range, script will be

let
    Source = Excel.CurrentWorkbook(){[Name="Range"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(
        Source,
        "Column1",
        Splitter.SplitTextByEachDelimiter({",",","}, QuoteStyle.None, false),
        {"Date", "Staff", "JSON"}
    ),
    trimColon = Table.ReplaceValue(
        #"Split Column by Delimiter",
        ":"":",""":",
        Replacer.ReplaceText,{"JSON"}
    ),
    #"Parsed JSON" = Table.TransformColumns(
        trimColon,
        {{"JSON", Json.Document}}
    ),
    columnNames = Record.FieldNames(#"Parsed JSON"[JSON]{0}),
    #"Expanded JSON" = Table.ExpandRecordColumn(
        #"Parsed JSON",
        "JSON", columnNames, columnNames
    ),
    #"Changed Type with Locale" = Table.TransformColumnTypes(
        #"Expanded JSON",
        {{"Date", type date}, {"DOB", type date}},
        "en-US"
    )
in
    #"Changed Type with Locale"

Resources