Forum Discussion

wiktorius1984's avatar
wiktorius1984
Regular Visitor
5 years ago
Solved

count if

Hi, I would like to get the effect as below, the value is calculated based on the two id's from the first two columns. id second_id count a 1 1 a 1 2 a 1 3 a 2 1 b 1 1 ...
Need Help
Show and Tell
FrankAT's avatar
FrankAT
Community Champion
5 years ago

Hi wiktorius1984 ,

you can do it in Power Query like this:

 

 

// Table (2)
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitXBzTICs5LgYkkoYuisZIi6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, second_id = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, Table, {"Index.1"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Index"}, {"Index.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Index"})
in
    #"Removed Columns"

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitXBzTICs5LgYkkoYuisZIi6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, second_id = _t]),
    #"Inserted Merged Column" = Table.AddColumn(Source, "Merged", each Text.Combine({[id], Text.From([second_id], "de-DE")}, ""), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Merged Column", {"Merged"}, {{"Count", each _, type table [id=nullable text, second_id=nullable number, count=nullable number, Merged=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1, 1, Int64.Type)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index"}, {"Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Index", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index.1", 0, 1, Int64.Type)
in
    #"Added Index"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)