Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Proud to be a Super User!
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.
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.
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
Proud to be a Super User!
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
Proud to be a Super User!