CODEWITHSUNDEEP
excelcsvpowerbi
Binaya Sharma
Binaya Sharma

Reputation: 1

Retriving value from one excel coulmn and put it in another column

I have one excel file that has multiple columns and 100s rows of data. In one column there are so many items separated by a comma. For example: "name":"harry", "id":"2", "operation": "file-deleted". I want to get some of those values in a separate column. I tried converting text to a column but didn't work. Question is in red column and solution in the green column

enter image description here

Upvotes: 0

Views: 34

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

Lets you have this below data in your table-

enter image description here

And delimiters are as below without space-

","
":"

If you have data in above shown format, go to Advance Editor of your table and incorporate this below steps there-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilHKS8xNjVGyilHKSCwqqoxR0olRSkyHiBgZg7mZKRAemJNfkFqUWJKZnwcWS8vMSdVNSc1JLUkFKlKK1aG/iaakmhgLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t]),

    //-- New Steps Started From Here
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "data"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"data", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "data", "data - Copy"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"data - Copy", Splitter.SplitTextByDelimiter(""",""", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "data - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"data - Copy", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "data - Copy", Splitter.SplitTextByDelimiter(""":""", QuoteStyle.None), {"data - Copy.1", "data - Copy.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"data - Copy.1", type text}, {"data - Copy.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2","""","",Replacer.ReplaceText,{"data - Copy.1", "data - Copy.2"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[#"data - Copy.1"]), "data - Copy.1", "data - Copy.2")
in
    #"Pivoted Column"

You will have a final output as below-

enter image description here

**Note: **If you have space in your delimiters as below, you can adjust the code accordingly. But you should have a common pattern for your delimiters.

", "
": "

Upvotes: 0

Related Questions