User Profile
lipesouza12
Frequent Visitor
Joined 4 years ago
User Widgets
Contributions
Use variables as column names in List Selects
Hi Community, Today I'm stuck on a simple list selection. I need to load the KPI from another table according to the group the salesperson belongs to. One possibility would be an IF statement but the groups may vary, in this case I would like to select the Goals Table Column from the group the seller belongs to. I tried declaring a variable and calling it as column but it didn't work. let Group = [GROUP] in Goals & Group{List.PositionOf(Goals[KPI], "AMOUNT")} And Tried let Group = [GROUP] in Goals{List.PositionOf(Goals[KPI], "AMOUNT")}Group But both return me Expression.SyntaxError: Expected token Eof. Another problem, is the dimension of the table that exceeds the thousands of rows heres the table sample GOALS KPI COMERCIAL ACTIVE SALES KPI 4010922 2683778 SERVICES KPI 44002 2944251 RENEWS KPI 241812 1618 MONETIZE KPI 43693 29236 AMOUNT 4340429 5658883 An table PERFORMANCE SELLER GROUP KPI SELLER 1 COMERCIAL SELLER 2 COMERCIAL SELLER 3 ACTIVE SELLER 4 ACTIVE SELLER 5 COMERCIAL SELLER 6 COMERCIALSolved2.7KViews0likes4CommentsUse variables as column names in List Selects
Hi Community, Today I'm stuck on a simple list selection. I need to load the KPI from another table according to the group the salesperson belongs to. One possibility would be an IF statement but the groups may vary, in this case I would like to select the Goals Table Column from the group the seller belongs to. I tried declaring a variable and calling it as column but it didn't work. let Group = [GROUP] in Goals & Group{List.PositionOf(Goals[KPI], "AMOUNT")} And Tried let Group = [GROUP] in Goals{List.PositionOf(Goals[KPI], "AMOUNT")}Group But both return me Expression.SyntaxError: Expected token Eof. Another problem, is the dimension of the table that exceeds the thousands of rows heres the table sample GOALS KPI COMERCIAL ACTIVE SALES KPI 4010922 2683778 SERVICES KPI 44002 2944251 RENEW KPI 241812 1618 MONETIZE KPI 43693 29236 AMOUNT 4340429 5658883 An table PERFORMANCE SELLER GROUP KPI PATRICIA COMERCIAL CELIA COMERCIAL LUCIANA ACTIVE HELEN ACTIVE ANA COMERCIAL AURELIO COMERCIAL480Views0likes2Comments- 747Views0likes0Comments
Group By with CountIf and SumIf
Hello everybody, I have a question that may have a very simple solution, but I haven't found it yet. I need to group a 300k rows table and perform conditional calculations as below. Product Month Year Total Item 1 MAY-2022 2022 300,00 Item 2 MAY-2022 2022 1386,67 Item 3 JUN-2022 2022 333,33 Item 1 JUN-2022 2022 190,00 Item 1 JUN-2022 2022 200,00 Item 1 JUL-2022 2022 250,00 Item 4 JUL-2022 2022 400,00 Item 5 AUG-2022 2022 2035,64 Item 1 AUG-2022 2022 200,00 I need to group by Month Column and SUM the Total Column if Product column is equal to a Name Product conditional. I always have five items. I need the result below. Month Item 1 Item 2 Item 3 Item 4 Item 5 MAY-2022 300,00 1386,67 0,00 0,00 0,00 JUN-2022 390,00 0,00 333,33 0,00 0,00 JUL-2022 250,00 0,00 0,00 400,00 0,00 AUG-2022 200,00 0,00 0,00 0,00 2035,64 As I said, there must be a better solution than creating conditional columns of 300k rows before grouping by.Solved804Views0likes2CommentsRe: Relative Count If M Language WITHOUT Groupig
Vijay_A_Verma I've tried both methods and your method's performance is much more efficient. Using a buffered table works fine with small bases, but using it for the base I intended performance drops taking hours of processing. Thanks again, it's saved me tons of hours of work!1.6KViews0likes0CommentsRe: Relative Count If M Language WITHOUT Groupig
Thanks a lot Vijay! Its works perfectly! However, as soon as I posted the question, I found another solution, but it's takes 1 minute to index 1.000 rows. I will test both methods for performance checking, since the table has 200 to 300 thousand rows. let Repetition = Excel.CurrentWorkbook(){[Name="YEARS"]}[Content][Column1]{0}, #"ℹ - Source" = Table.Repeat(Excel.CurrentWorkbook(){[Name="TableTest"]}[Content], Repetition ), #"⯐ - Temp Index" = Table.AddIndexColumn(#"ℹ - Source", "Index", 1, 1, Int64.Type), BufferedTable = Table.Buffer(#"⯐ - Temp Index"), #"∳ - Single row count" = Table.AddColumn(BufferedTable, "Count", (OT) => Table.RowCount(Table.SelectRows(BufferedTable, (IT) => IT[Index] <= OT[Index] and IT[COND. 1] = OT[COND. 1] and IT[COND. 2] = OT[COND. 2]and IT[COND. 3] = OT[COND. 3]) ) ), #"⯐ - Indexer" = Table.AddColumn(#"∳ - Single row count", "INDEXER", each Text.From([Count]) & "-" & [COND. 3]), #"Remove Columns" = Table.RemoveColumns(#"⯐ - Indexer",{"Index", "Count"}) in #"Remove Columns" Follow link: https://www.youtube.com/watch?v=kjOmNPoVDYs1.6KViews0likes0CommentsRelative Count If M Language WITHOUT Groupig
Hello everybody, I have a question that may have a simple answer but I don't see it. I need an indexer that identifies how many times a given row repeats. Working exactly like rexcel's relative COUNTIF "=countifs($X1:X1;"01.1";...)" The original table has single rows where I need to, variably, duplicate it N times. For that, I'm using Table.Repeat To relate it to another table, I need a unique identifier that identifies the 1st time the row appears, the 2nd, 3rd and so on... I need to create a index by row or table, since the table is repeated N times COND. 1 COND. 2 COND. 3 COUNT IF A T207 02.1 1 A T207 02.2 1 B T207 02.3 1 A T207 02.1 2 A T207 02.2 2 B T207 02.2 3 A 101 02.1 1Solved1.7KViews0likes3Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.