Split column into several columns per number of interview score

[Anonymous]

Hi community,

I've got th following column which I'd like to split so as to separate the interview scores (one interview score per column)  

Desired outcome

Interview Score 1	Interview Score 1	Interview Score 1	Interview Score 1	Interview Score 1
3	3+	2.5	3.5	3

Here's the full list of all possible interview scores

Thank you for your help

Giac

[Thingsclump · Accepted solution]

Hi @Anonymous 

Below is the solution. Copy the code at the end and it should work. Only exception is that you can not have same column name multiple times in PQ. In your desired outcome, "Interview Score 1" column is repeated mutlple times and its not allowed in PQ.

Input.

Result

Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrKL81LUTC0UjDWVoCwjZDYxkC2nqlSrE60khOKYiS1YFlnFFml2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Candidate = _t, #"Interview score" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Candidate", type text}, {"Interview score", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Interview score", Splitter.SplitTextByDelimiter("Round", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Interview score"),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Interview score] <> "")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," ","",Replacer.ReplaceText,{"Interview score"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value", "Interview score", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Interview score.1", "Interview score.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Interview score.1", "Interview score"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each "Interview score " & [Interview score]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Interview score"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Interview score.2")
in
#"Pivoted Column"

Accpet it as a solution if it answers your question!

Thanks

thingsclump

www.thingsclump.com 

[Anonymous]

This is brilliant! Thank you, @Thingsclump 

I've noticed that I get what appears to be duplicates. I think this may be because there's a space after the numbers. Any idea on how to bulk replace them  

Ideally, I'd like to get to the following conversion

[Thingsclump]

Hi @Anonymous 

You can select values column and replace " " (space) with nothing. It should work.

For conversion, you can add a conditional column as below before pivoting step and use it.

Thank you.

[Anonymous]

I tried removing " " (space) as per your suggestion but it didn't work so maybe it's not a space? What I've also found out is that there appears to be duplicates in Power Query, but when I apply the changes and open Power BI in table view the duplicates don't appear to be there anymore. Perhaps I would convert the interview scores using DAX instead of M?

The desired end result is the average of all scores, hence why I need to convert them.

[Thingsclump]

@Anonymous 

Select the numbers column and do "trim". that should remove charecters at end and last.

Please use "enter data" in PQ to create your input data table and then do all operations.. Share the M Code and we can see. I strongly believe cleanup should be done in PQ and not DAX/