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.
Hello,
To continue the project for which Jimmy801 (Re: TCD from Database) gave me an answer, I notice that I have duplicates in the database, and that these duplicates should not be deleted. prior to the Pivot solution I must therefore ensure that there are no more duplicates, to do this I must to get the rank of each identical occurrence (Duplicate) in a column, and concatenate it with the content of another columnn
starting point:
Ident | Rubrique |
2630775108002' | S21.G00.50.008 |
2630775108002' | S21.G00.50.009 |
2630775108002' | S21.G00.51.001 |
2630775108002' | S21.G00.51.001 |
2630775108002' | S21.G00.51.001 |
2630775108002' | S21.G00.51.001 |
2630775108002' | S21.G00.51.002 |
2630775108002' | S21.G00.51.002 |
2630775108002' | S21.G00.51.002 |
2630775108002' | S21.G00.51.002 |
2630775108002' | S21.G00.53.001 |
2630775108002' | S21.G00.53.001 |
2630775108002' | S21.G00.53.002 |
2630775108002' | S21.G00.53.002 |
desired outcome
Ident | Rubrique | Rank | ident&rank |
2630775108002' | S21.G00.50.008 | 1 | 2630775108002'_1 |
2630775108002' | S21.G00.50.009 | 1 | 2630775108002'_1 |
2630775108002' | S21.G00.51.001 | 1 | 2630775108002'_1 |
2630775108002' | S21.G00.51.001 | 2 | 2630775108002'_2 |
2630775108002' | S21.G00.51.001 | 3 | 2630775108002'_3 |
2630775108002' | S21.G00.51.001 | 4 | 2630775108002'_4 |
2630775108002' | S21.G00.51.002 | 1 | 2630775108002'_1 |
2630775108002' | S21.G00.51.002 | 2 | 2630775108002'_2 |
2630775108002' | S21.G00.51.002 | 3 | 2630775108002'_3 |
2630775108002' | S21.G00.51.002 | 4 | 2630775108002'_4 |
2630775108002' | S21.G00.53.001 | 1 | 2630775108002'_1 |
2630775108002' | S21.G00.53.001 | 2 | 2630775108002'_2 |
2630775108002' | S21.G00.53.002 | 1 | 2630775108002'_1 |
2630775108002' | S21.G00.53.002 | 2 | 2630775108002'_2 |
thank you for help from members or super users
thank you in advance
Philippe Muniesa
Solved! Go to Solution.
@PhilippeMuniesa , it's necessary to index the table in Power Query to differentiate identical rubriques within a ident group; then you can use such a calculated column,
Rank =
DS[Rubrique] & "_"
& RANKX (
FILTER ( DS, DS[Rubrique] = EARLIER ( DS[Rubrique] ) ),
DS[Index],
,
ASC
)
Or you might want to try a solution in Power Query,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIzNjA3NzU0sDAwMIopBRLmSjpKwUaGeu4GBnqmBnoGBhZKsTpEKbQkRqEhUKHhEFBoNMgVGhPrGZIUEm01UGEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ident = _t, Rubrique = _t]),
#"Grouped Rows" = Table.RemoveColumns(Table.Group(Source, {"Ident", "Rubrique"}, {{"ar", each Table.AddIndexColumn(_, "Index", 1)}}), {"Ident","Rubrique"}),
#"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"Ident", "Rubrique", "Index"}, {"Ident", "Rubrique", "Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded ar", "ident&rank", each [Rubrique] & "_" & Text.From([Index]))
in
#"Added Custom"
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! |
@PhilippeMuniesa , it's necessary to index the table in Power Query to differentiate identical rubriques within a ident group; then you can use such a calculated column,
Rank =
DS[Rubrique] & "_"
& RANKX (
FILTER ( DS, DS[Rubrique] = EARLIER ( DS[Rubrique] ) ),
DS[Index],
,
ASC
)
Or you might want to try a solution in Power Query,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIzNjA3NzU0sDAwMIopBRLmSjpKwUaGeu4GBnqmBnoGBhZKsTpEKbQkRqEhUKHhEFBoNMgVGhPrGZIUEm01UGEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ident = _t, Rubrique = _t]),
#"Grouped Rows" = Table.RemoveColumns(Table.Group(Source, {"Ident", "Rubrique"}, {{"ar", each Table.AddIndexColumn(_, "Index", 1)}}), {"Ident","Rubrique"}),
#"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"Ident", "Rubrique", "Index"}, {"Ident", "Rubrique", "Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded ar", "ident&rank", each [Rubrique] & "_" & Text.From([Index]))
in
#"Added Custom"
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! |
You'll need to add a basic index column for this to work, which can be added in Power Query> Add columns>index column.
Be sure to change 'Table' with the name of the table you're using.
ident&rank =
VAR _Occurrences =
CALCULATE (
COUNT ( [Rubrique] ),
FILTER (
'Table',
[Index] <= EARLIER ( 'Table'[Index] )
&& [Rubrique] = EARLIER ( 'Table'[Rubrique] )
)
)
RETURN
'Table'[Ident] & "_" & _Occurrences
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 |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |