Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I have a DAX calculated column to count the rows of the table with specific value in a column and it works fine. Now I want the same to be written in Power Query's custom column but getting error.
My DAX query is
CALCULATE(COUNTROWS('Table'), 'Table'[TEST] = "No")
The above calcultation returns the number of rows in the Table table that has "No" value in the TEST column
I tried the below in Power Query but it is giving me the "Expression.Error: A cyclic reference was encountered during evaluation" error.
Table.RowCount(
Table.SelectRows(#"Table",
each Text.Contains ([TEST], "No")))
How to fix this?
Solved! Go to Solution.
Thanks Edhans.
I am creating this Row count column #"Added Custom 8" only after the custom column #"Added Custom 7" I created previously:
And I am creating the custom column from Add Column ribbon -> Custom Column and below is the code I have:
And when I click OK and run, getting the below error:
Text.Contains is wrong. It should be Text.Contains([Test], "No")
You have one argument with an equal sign. Get rid of that and replace with a comma, for 2 arguments.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @PowerUser39 ,
Thanks for @edhans reply.
Loop dependencies usually occur when you try to refer to yourself in the same step or use undefined values in a calculation. To avoid this problem, you can break the calculation into multiple steps.
Sample data
I create a new column named TEST.1 and calculate the sum of NO
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vNXitWJVop0DQbTUC6aKJiOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TEST = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TEST", type text}}),
AddCustom = Table.AddColumn(Source, "TEST.1", each [TEST]),
CountNO = Table.AddColumn(AddCustom, "CountNO", each if [TEST.1] = "NO" then 1 else 0),
TotalNO = List.Sum(CountNO[CountNO]),
AddResult = Table.AddColumn(CountNO, "Result", each TotalNO),
RemoveCountNO = Table.RemoveColumns(AddResult, {"CountNO"})
in
RemoveCountNO
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@Anonymous Thanks for your response. I tried it your method too but getting only the entire list count and not the conditional row count.
Not sure why it isn't working. Do you have an "each" operator before the Table.RowCount()
Here is full code that works:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyBUymgsm0ovx0MIOAMjCZnp+ThsRFI2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]),
#"Added custom" =
Table.AddColumn(
Source,
"Custom",
each
Table.RowCount(Table.SelectRows(Source, each Text.Contains([Product], "f")))
)
in
#"Added custom"
there are 7 records that have an "f" in them.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans Thanks for your response. Your sample code works fine and I found the problem on my end.
The TEST column I have in my condition is another custom Added column which is not in the source.
Table.RowCount(
Table.SelectRows(Source,
each Text.Contains ([TEST], "No")))
So my question is how to create a custom column in Power Query to count rows in the table that has "No" value in another custom column?
Please help
Not sure I understand. Your Row Count/Table Select should be after you have this other custom column created. So look at this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyBUymgsm0ovx0MIOAMjCZnp+ThsRFI2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]),
AddNewColumn =
Table.AddColumn(
Source,
"New Column",
each if [Product] = "a" then "f" else [Product]
),
#"Added custom" =
Table.AddColumn(
AddNewColumn,
"Custom",
each
Table.RowCount(Table.SelectRows(AddNewColumn, each Text.Contains([New Column], "f")))
)
in
#"Added custom"
The #"Added Custom" column where the Row Count and Table Select Rows is happening is referencing the AddNewColumn staep above where a new column was created that changes the "a" to an "f"
So the count goes from 7 to 9.
Please post more than just a formula for your custom column. You'll notice I am posting the code from the advanced editor. We have no idea what steps are before and after your custom column function, and that knowledge is critical to building it correctly.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks Edhans.
I am creating this Row count column #"Added Custom 8" only after the custom column #"Added Custom 7" I created previously:
And I am creating the custom column from Add Column ribbon -> Custom Column and below is the code I have:
And when I click OK and run, getting the below error:
Text.Contains is wrong. It should be Text.Contains([Test], "No")
You have one argument with an equal sign. Get rid of that and replace with a comma, for 2 arguments.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingOk I changed it and it worked but the result is basically the entire row count in the table and not based on the condition we have to count only the rows with "No" value in TEST column. I want the same DAX output (116) in Power Query and not the entire table row count which is 8940
Any other way of writing this to get the intended result @edhans?
You aren't sharing enough information. In my screenshots I am showing you the table and the answer. 17 records, 9 that match (in the second example). It isn't returning 17, the entire table record count. Showing two cards on the report with different values isn't helpful. What are those measures? Are you adding up, in my example, the 9 17 times and not just getting the distinct value?
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans Thanks for the detailed explanation. I rebuilt the report again with the required columns alone with your approach and it worked.