Office

In Office

On 16/12/2024

Group Data Table

Id	Group	Name	Group	Count
1	Group1	Dietrich	Group1	3
2	Group1	toto	Group2	2
3	Group1	titi	Group3	1
4	Group2	nana
5	Group2	popo
6	Group3	pipi

Unique value : =UNIQUE(B2:B7), for a specific sheet : =UNIQUE(Append1_1!F2:F337888)// here Append1_1 is the name of the sheet

Count unique value : =COUNTIF(B2:B7,F1)

Group	Count
Group1	3
Group2	2
Group3	1

In Office

Excel Power Query Functions

On 16/12/2024

Power Query Functions with Examples

Function	Description	Example
`Table.AddColumn`	Adds a new column to a table.	`Table.AddColumn(Source, "NewColumn", each [OldColumn] * 2)`
`Table.RemoveColumns`	Removes one or more columns from a table.	`Table.RemoveColumns(Source, {"Column1", "Column2"})`
`Table.SelectRows`	Filters rows based on a condition.	`Table.SelectRows(Source, each [Age] > 18)`
`Table.Sort`	Sorts a table by one or more columns.	`Table.Sort(Source, {{"ColumnName", Order.Ascending}})`
`Text.Upper`	Converts text to uppercase.	`Text.Upper("hello")` returns `HELLO`
`Text.Lower`	Converts text to lowercase.	`Text.Lower("HELLO")` returns `hello`
`Text.Combine`	Combines a list of text values into one.	`Text.Combine({"Hello", "World"}, " ")` returns `Hello World`
`Number.Round`	Rounds a number to a specified number of digits.	`Number.Round(3.14159, 2)` returns `3.14`
`DateTime.LocalNow`	Returns the current date and time.	`DateTime.LocalNow()`
`List.Sum`	Calculates the sum of a list of numbers.	`List.Sum({1, 2, 3, 4})` returns `10`
`List.Distinct`	Removes duplicate values from a list.	`List.Distinct({1, 2, 2, 3})` returns `{1, 2, 3}`
`Record.Field`	Gets the value of a field in a record.	`Record.Field([Record], "FieldName")`
`Record.AddField`	Adds a field to a record.	`Record.AddField([Record], "NewField", 123)`
`Table.AddColumn`	Adds a new column to a table.	`Table.AddColumn(Source, "NewColumn", each [OldColumn] * 2)`
`Table.RemoveColumns`	Removes one or more columns from a table.	`Table.RemoveColumns(Source, {"Column1", "Column2"})`
`Table.SelectRows`	Filters rows based on a condition.	`Table.SelectRows(Source, each [Age] > 18)`
`Table.Sort`	Sorts a table by one or more columns.	`Table.Sort(Source, {{"ColumnName", Order.Ascending}})`
`Text.Upper`	Converts text to uppercase.	`Text.Upper("hello")` returns `HELLO`
`Text.Lower`	Converts text to lowercase.	`Text.Lower("HELLO")` returns `hello`
`Text.Combine`	Combines a list of text values into one.	`Text.Combine({"Hello", "World"}, " ")` returns `Hello World`
`Number.Round`	Rounds a number to a specified number of digits.	`Number.Round(3.14159, 2)` returns `3.14`
`DateTime.LocalNow`	Returns the current date and time.	`DateTime.LocalNow()`
`List.Sum`	Calculates the sum of a list of numbers.	`List.Sum({1, 2, 3, 4})` returns `10`
`List.Average`	Calculates the average of a list of numbers.	`List.Average({1, 2, 3, 4})` returns `2.5`
`List.Max`	Returns the maximum value from a list.	`List.Max({1, 2, 3, 4})` returns `4`
`List.Min`	Returns the minimum value from a list.	`List.Min({1, 2, 3, 4})` returns `1`
`List.Count`	Counts the number of items in a list.	`List.Count({1, 2, 3, 4})` returns `4`
`List.Distinct`	Removes duplicate values from a list.	`List.Distinct({1, 2, 2, 3})` returns `{1, 2, 3}`
`List.Length`	Returns the length of a list.	`List.Length({1, 2, 3, 4})` returns `4`
`Record.Field`	Gets the value of a field in a record.	`Record.Field([Record], "FieldName")`
`Record.AddField`	Adds a field to a record.	`Record.AddField([Record], "NewField", 123)`