Forum Discussion
Countifs
I need a power query custom column formula like the one shown in excel
Can somebody help? :=)
Its should count if its the same mRID and month
- Prepare your table to this format:
2. Name your Query i.e. MyData (without spaces and special characters)
3. Create blank query, open advanced editor, select whole code and replace it with the code I provided above.
4. Close Advanced Editor
5. Select Source Step from right panel Query Settings
6. Delete whole code of Source step (as you can see selected in the picture in Note below in my signature) and write there = MyData (which is your table reference)
8 Replies
Hi AndreasMEG
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR0lUwMDAyBlaGRsrBSrQ4SkEVE6jeCSJpiShnBJU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Budget = _t, mRID = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Budget", Currency.Type}, {"mRID", Int64.Type}}, "sk-SK"), GroupedRows = Table.Group(ChangedType, {"Date", "mRID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table}}), ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Budget"}, {"Budget"}) in ExpandedAllI already have a table in power bi
Sources are from API's
Need a custom column formula which is simular to the excel formula
Hope it makes sence, and thanks! 🙂
Makes sence, perfect! Thank you so much! 😄
I changed the code a tiny bit (mRID - to text.type)
let Source = MyData, ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Budget", Currency.Type}, {"mRID", Text.Type}}, "sk-SK"), GroupedRows = Table.Group(ChangedType, {"Date", "mRID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table}}), ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Budget"}, {"Budget"}) in ExpandedAllYou're welcome.
