How to transform one column data into two column like one for runs and one for wickets

[Girish_123]

[Nathaniel_C · Accepted solution]

Hi @Girish_123 
In pq choose add column, conditional column.


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[AlexisOlson · Accepted solution]

If the runs and wickets are grouped together somehow, then I'd suggest splitting the [Margin] column and then pivoting.

Start:

Split:

Pivot on [Type] with [Count] as values:

Sample query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI3VigqzStWitWB8A0NFMozk7NTSyBCRkAhCwwRQxMzhC5joIAxihIToIgZhogJVEssAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Game = _t, Margin = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Margin", type text}, {"Game", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Margin", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Count", "Type"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Count", Int64.Type}, {"Type", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Type]), "Type", "Count")
in
    #"Pivoted Column"

If the runs and wickets aren't related somehow, then it isn't clear how to align them in two separate columns and it would be helpful to include an example of what you want the result to look like.

[Nathaniel_C · Accepted solution]

Hi @Girish_123 
In pq choose add column, conditional column.


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @Girish_123 ,
You could take this a couple of steps further: pics are in alternate order.  fill down, fill up, remove rows, add index for at bat
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Add index and call it at batRemove alternate rowsFill Down Wickets, the fill up the second col Runs