Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
Im trying to count Yes/No/NA occurrences in the columns that starts with a scpecific string. Doing in this way will permit use the same code in tables with different number of columns.
If the columns are hardcoded, it works. I get a new calculated column with the count of "Not Applicable" in this example.
QGROUP1.CountNA = Table.AddColumn(GROUP1.CountNo, "GROUP1.CountNA", each List.Count(List.Select({[GROUP1.1], [GROUP1.2], [GROUP1.3], [GROUP1.4], [GROUP1.5], [GROUP1.6], [GROUP1.7], [GROUP1.8]},each _ = "Not Applicable")),Int64.Type),
But I fail trying to generate dinamically the columns name list
{[GROUP1.1], [GROUP1.2], [GROUP1.3], [GROUP1.4], [GROUP1.5], [GROUP1.6], [GROUP1.7], [GROUP1.8]}
Two approaches
MyList1= List.Select(Table.ColumnNames(#"Reordered Columns"), each Text.StartsWith(_, "GROUP1")),
MyList2=List.Transform(MyList1, each "[" & _ & "]"),
MyList1 generates succesfully a list with the column names starting with GROUP1
MyList2 is the same list with the brackets added for each item.
But the following fails, it counts 0 using MyList1 or MyList2. Using the hardcoded list obviously works, like N/A case above.
GROUP1.CountYes = Table.AddColumn(#"Reordered Columns", "GROUP1.CountYes", each List.Count(List.Select(MyList1,each _ = "Yes")),Int64.Type),
GROUP1.CountNo = Table.AddColumn(GROUP1.CountYes, "GROUP1.CountNo", each List.Count(List.Select(MyList2,each _ = "No")), Int64.Type),
My guess is that is something related to the type of the items in the list - text?
Any help is appreciated
Thank you very much
Solved! Go to Solution.
Hi.
I have found this approach in
MyCols = List.Buffer(List.Select(Table.ColumnNames(#"Reordered Columns"), each Text.StartsWith(_, "GROUP_"))),
GROUP_1_Yes = Table.AddColumn(#"Reordered Columns" , "GROUP_1_Yes", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,MyCols)), each _ = "Yes")) ,Int64.Type),
Thank you all for your help!
🙂
Hi.
I have found this approach in
MyCols = List.Buffer(List.Select(Table.ColumnNames(#"Reordered Columns"), each Text.StartsWith(_, "GROUP_"))),
GROUP_1_Yes = Table.AddColumn(#"Reordered Columns" , "GROUP_1_Yes", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,MyCols)), each _ = "Yes")) ,Int64.Type),
Thank you all for your help!
🙂
what HTML error exactly? Where?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
If it's a matter of having more or less columns, but the column names themselves do not change, then you can have a list of all the possible columns in a list in a separate query (perhaps using Table.ColumnNames(TableWithAllTheColumns), but get them all in a list, named Columns. Then just:
Table.SelectColumns(TableName, Columns, MissingField.Ignore)
The MissingField.Ignore parameter will look for all of the columns in the Columns list, and if the columns do not exist, they get ignored. Those that do exist get selected.
--Nate
It is a nice option for your scenario where names do not change. In my tables the columns to be used start with a specific string but they are different in every table. Table 1 (GROUP1.1 GROUP1.2) Table 2 (GROUP2.1 GROUP2.2)
Thanks!
Best regards
Hi @CarlosFF
Can you share the table (a few rows would suffice) you are trying to apply this on? So that we can copy the contents and run a couple of tests.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi. The table looks aprox like this.
I am sorry but I do not see how to upload the table. It is my first post in this forum 😞 🙂
Hi, @CarlosFF , it's simple enough like this,
GROUP1.CountYes = Table.AddColumn(#"Reordered Columns", "GROUP1.CountYes", each List.Count(List.Select(Record.ToList(_),each _ = "Yes")),Int64.Type),
GROUP1.CountNo = Table.AddColumn(GROUP1.CountYes, "GROUP1.CountNo", each List.Count(List.Select(Record.ToList(_),each _ = "No")), Int64.Type),
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi. It looks simple.
Where you put the list name?
GROUP1.CountNo = Table.AddColumn(GROUP1.CountYes, "GROUP1.CountNo", each List.Count(List.Select(Record.ToList(MYLIST),each _ = "No")), Int64.Type),
As argument of Record.ToList it gives the following error, using MyList1 or MyList2 (items with brackets)
Expression.Error: We cannot convert a value of type List to type Record.
Details:
Value=[List]
Type=[Type]
Thanks!
You can just paste it here as normal text. Use the copy table option in PQ an paste here.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Nice feature. It pastes correctly but when saving gives an HTML error. I have tried copying from excel and power Bi. No way. Also doing the table manually. Weird.
Best regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |