Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table in this form:
Org | Case |
A | FA |
B | SI |
A | SI |
C | SI |
B | SI |
C | FA |
A | FA |
I'd like to have the target table results such that all the occurrence of FA or SI are counted like so:
ORG | FA | SI |
A | 2 | 1 |
B | 2 | 0 |
C | 1 | 1 |
How can I achieve this? Thanks
Solved! Go to Solution.
Hello @godey4me ,
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KV9JRck4sTlWK1YlWcgRy3BzBTCcgM9gTLgplOiOYTqiiUG0wE2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Grouped Rows" = Table.Group(
#"Promoted Headers",
{"Org", "Case"},
{{"Count", each Table.RowCount(_), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(
#"Grouped Rows",
List.Distinct(#"Grouped Rows"[Case]), "Case", "Count", List.Sum)
in
#"Pivoted Column"
Hello @godey4me ,
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KV9JRck4sTlWK1YlWcgRy3BzBTCcgM9gTLgplOiOYTqiiUG0wE2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Grouped Rows" = Table.Group(
#"Promoted Headers",
{"Org", "Case"},
{{"Count", each Table.RowCount(_), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(
#"Grouped Rows",
List.Distinct(#"Grouped Rows"[Case]), "Case", "Count", List.Sum)
in
#"Pivoted Column"