Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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