Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello I merged 4 tables, that have duplicate values, I want to keep only the one that has the earliest date for each "Lote" (ignoring case), how can I fix this? The code I have removes duplicates but not in a defined order:
let Source1 = Table.AddColumn(PNC_TELA, "Source", each "PNC_TELA"), Source3 = Table.AddColumn(TONO_MALO, "Source", each "TONO_MALO"), #"Removed Other Columns" = Table.SelectColumns(Source3,{"Fecha de Ingreso", "Lote", "Defecto 1", "Disposición", "CatCausa", "Causa", "Comentarios", "Proceso Responsable", "Source"}), Source2 = Table.AddColumn(PNC_PARTES_CORTADAS, "Source", each "PNC_PARTES_CORTADAS"), Source4 = Table.AddColumn(VARIACION_TONO, "Source", each "VARIACION_TONO"), SourceMerge = Table.Combine({#"Removed Other Columns",Source4, Source1, Source2}), #"Filtered Rows1" = Table.SelectRows(SourceMerge, each [Lote] <> null and [Lote] <> ""), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Fecha de Ingreso", type date}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each true), #"Remove Duplicates" = Table.Distinct(#"Filtered Rows", { "Lote", Comparer.OrdinalIgnoreCase }) in #"Remove Duplicates"
Best Regards
Hi, please post a Data Sample to replicate your case.
Regards
Victor
Lima - Peru
This does not work as I intend, due to the values in the other columns are not the same, keeps duplicate "lote". I need something that determines the minimum date for each "lote" and keep that row only.
Your data is like this:
Lote OTHERCOL1 OTHERCOL2 Date
AAA1 FFFF GGGGG 15/02/17
AAA2 GGFFD RRRRR 11/02/17
AAA1 RERER GGGF 03/02/17
AAA2 RRR AAASD 15/02/17
And you want to keep this:
Lote OTHERCOL1 OTHERCOL2 Date
AAA1 RERER GGGF 03/02/17
AAA2 GGFFD RRRRR 11/02/17
Its ok this.
Exactly,
what this does, is that shows only dates on the listings, causing even more duplicates on my data as in you date those dates do no duplicate it you do not see it on your query, please review my data sample once ome maybe I made a mistake while replicating what is on the image.
This kept the table as before the merge, based on you idea tried something on the query, this finds the earliest date on the table, then I just have to filter all "true", I need to do the same thing but earliest date for each batch in order to get the result Im looking for.
Maybe you can help me review.
Here is the M code:
let Source1 = Table.AddColumn(PNC_TELA, "Source", each "PNC_TELA"), Source3 = Table.AddColumn(TONO_MALO, "Source", each "TONO_MALO"), #"Removed Other Columns" = Table.SelectColumns(Source3,{"Fecha de Ingreso", "Lote", "Defecto 1", "Disposición", "CatCausa", "Causa", "Comentarios", "Proceso Responsable", "Source"}), Source2 = Table.AddColumn(PNC_PARTES_CORTADAS, "Source", each "PNC_PARTES_CORTADAS"), Source4 = Table.AddColumn(VARIACION_TONO, "Source", each "VARIACION_TONO"), SourceMerge = Table.Combine({#"Removed Other Columns",Source4, Source1, Source2}), #"Filtered Rows1" = Table.SelectRows(SourceMerge, each [Lote] <> null and [Lote] <> ""), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Fecha de Ingreso", type date}}), #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each [Disposición] <> "APROBADO" and Text.StartsWith([Defecto 1], "3")), #"FindMinDate" = Table.AddColumn( #"Filtered Rows2", "MinDate", each if [Fecha de Ingreso]=List.Min(#"Filtered Rows2"[Fecha de Ingreso]) then "true" else "false") in FindMinDate
I can't review the Query Editor in your PBIX because is a external source.
To a easy solution can you send me a Excel file with part of the data (Combined the 4 sources and with the duplicates).
Regards
Victor
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
93 | |
86 | |
76 | |
64 |
User | Count |
---|---|
138 | |
113 | |
107 | |
98 | |
92 |