Sort and Filter Append Query

[ratercero]

Hello,

The Task is to append 3 queries, filter [Area Responsable]= "Tintoreria" and [Disposición] <> "Aprobado",  after this if duplicate "Lote" leave earliest result ( no duplicates)

I have been able to append the queries: 

let
    Source = Table.Combine({PNC_TELA, PNC_PARTES_CORTADAS, TONO_MALO}),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Fecha de Ingreso", "Lote", "Defecto 1", "Disposición", "Proceso Responsable"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Fecha de Ingreso", type date}})
in
    #"Changed Type"

One more question, Can I add a column to wach row telling me from which Database that line is from?

Thank you

[Eric_Zhang]

---

@ratercero wrote:

Hello,

The Task is to append 3 queries, filter [Area Responsable]= "Tintoreria" and [Disposición] <> "Aprobado",  after this if duplicate "Lote" leave earliest result ( no duplicates)

I have been able to append the queries: 

let
    Source = Table.Combine({PNC_TELA, PNC_PARTES_CORTADAS, TONO_MALO}),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Fecha de Ingreso", "Lote", "Defecto 1", "Disposición", "Proceso Responsable"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Fecha de Ingreso", type date}})
in
    #"Changed Type"

One more question, Can I add a column to wach row telling me from which Database that line is from?

Thank you

---

@ratercero

For the last question, you can add a column valuing the database name in each table before combining them.

For the first question, could you please post some sample data for the 3 tables and expected output?

[ratercero]

Hello @Eric_Zhang,

Sorry for the late response, here it is:

RT

[ratercero]

Any ideas anyone else?

[MarcelBeug]

@ratercero: the question was already answered by @Eric_Zhang:

"For the last question, you can add a column valuing the database name in each table before combining them."

Translated into code, e.g.:

let
    Source1 = Table.AddColumn(PNC_TELA, "Source", each "PNC_TELA"),
    Source2 = Table.AddColumn(PNC_PARTES_CORTADAS, "Source", each "PNC_PARTES_CORTADAS"),
    Source3 = Table.AddColumn(TONO_MALO, "Source", each "TONO_MALO"),
    Source = Table.Combine({Source1, Source2, Source3}),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Fecha de Ingreso", "Lote", "Defecto 1", "Disposición", "Proceso Responsable", "Source"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Fecha de Ingreso", type date}})
in
    #"Changed Type"

[ratercero]

Hello @MarcelBeug,

I used @Eric_Zhang recomendation and this is my code:

let
    Source1 = Table.AddColumn(PNC_TELA, "Source", each "PNC_TELA"),
    Source2 = Table.AddColumn(PNC_PARTES_CORTADAS, "Source", each "PNC_PARTES_CORTADAS"),
    Source3 = Table.AddColumn(TONO_MALO, "Source", each "TONO_MALO"),
    Source = Table.Combine({Source1, Source2, Source3}),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Fecha de Ingreso", "Lote", "Defecto 1", "Disposición", "Proceso Responsable", "Source"}),
    #"ChangedType" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Fecha de Ingreso", type date}}),
    #"Filtered Table" =Table.SelectRows(ChangedType, each [Proceso Responsable] = "Tintoreria" and [Disposición]<>"Aprobado" )
in
    #"Filtered Table"

It partially works for my final objective since I need to only the first time a batch # shows and

[Proceso Responsable] = "Tintoreria" and [Disposición]<>"Aprobado"

 , if it repeats with another defects the current code is leaving that entry too.

[MarcelBeug · Accepted solution]

You can sort on date and than remove duplicates as illustrated in this short video.

[ratercero]

@MarcelBeug , will I have to do this everytime I do the refresh? 

[MarcelBeug]

No: just add these steps to your query, so it will be part of the refresh.