Split Column in PowerBi-CodePudding

I have been stuck in splitting column in PowerBi. The below picture is the example of the data I would need to split.

In the qualification tab, I would want to seperate those details along with the dates to separate columns.

Do you think you can help me?

CodePudding user response：

You don't show what you want for a result. In particular you don't show if you want to retain the surrounding parentheses.

If you do want to retain them, then merely split on the transition by inserting this M-Code in the Advanced Editor in the place where you want to do the split.

let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Qualifications", type text}}),

//split on transition from " " to "("
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Qualifications", 
      Splitter.SplitTextByCharacterTransition((c) => c <> "(", {"("}), {"Qualifications", "Date"}),

//remove trailing " " from first column
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Character Transition",{{"Qualifications", Text.Trim, type text}})
in
    #"Trimmed Text"

If you want to remove the parentheses, then just split on the "(" and remove the trailing ")"

CodePudding user response：

I didn't notice at first that all the data is in a single row

So...

Right click the Qualifications column, split column ... by delimiter... [x] split using special character, and for the special character, choose line feed, and for advanced options [rows]

That gets you this, on different rows

Right click the qualifications column, split column .. by delimiter ..

Select or enter delimiter: choose custom and enter space(

Note: In step above I mean to actually use two characters - a space followed by a parenthesis

Right click the new column, Replace Values, and replace ) with nothing

Click select that column and transform ... data type .. date

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Qualifications", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Qualifications"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Qualifications", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Qualifications", Splitter.SplitTextByEachDelimiter({" ("}, QuoteStyle.Csv, false), {"Qualifications.1", "Qualifications.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Qualifications.1", type text}, {"Qualifications.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",")","",Replacer.ReplaceText,{"Qualifications.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Qualifications.2", type date}})
in  #"Changed Type2"