Merge values on 1 table of maching columns names of 3 tables remove rows that meet condition

[ratercero]

Hello,

New to Power BI and this is way out of my league so here it goes:

I have 3 Tables:

QA Report source 1

QA Report source 2

QA Report source 3

The QA reports have 5 matching columns and other specific for each table, the matching columns are:

Date
Batch #

Defect

Responsable Area

Final Disposition

 I'm looking to merge the 3 QA tables and sort the information by date. and then filter the ones that meet the following condition:

Earliest date of entry
Responsable Area = "Dyehouse"
Final disposition <> "Aprobado"


Thank you for the help!

[Sunkari]

@ratercero: (Power Query)Merge is used for combining three tables.

Need more info on following. Earliest date of entry at combined table level or you is expecting at each defect level.

I'm looking to merge the 3 QA tables and sort the information by date. and then filter the ones that meet the following condition:

Earliest date of entry
Responsable Area = "Dyehouse"
Final disposition <> "Aprobado"

[ratercero]

Hello @Sunkari,

The logic you sugest is exactly what im looking for, use Earliest date of entry at combined table level

Here is some aditional information about the tables:

Current query for table A

let
Source = Access.Database(File.Contents("V:\Calidad Acabado\PNC TELA ACABADA - Copy.accdb"), [CreateNavigationProperties=true]),
_PNC_TELA = Source{[Schema="",Item="PNC_TELA"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(_PNC_TELA,{{"Lote", type text}})
in
#"Changed Type"

Current query for table B

let
    Source = Access.Database(File.Contents("D:\Reynaldo Tercero\Desktop\PNC PARTES CORTADAS.accdb"), [CreateNavigationProperties=true]),
    _PNC_PARTES_CORTADAS = Source{[Schema="",Item="PNC_PARTES_CORTADAS"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(_PNC_PARTES_CORTADAS,{{"Lote", type text}})
in
    #"Changed Type"

Current query for table C

let
    Source = Access.Database(File.Contents("D:\Reynaldo Tercero\Desktop\TONO MALO TINTORERIA.accdb"), [CreateNavigationProperties=true]),
    _TONO_MALO = Source{[Schema="",Item="TONO_MALO"]}[Data]
in
    _TONO_MALO

Let me know if you need more information
RT

[Eric_Zhang]

Check

let
   #"QA Report source 1" = Table.FromRows({{"2017-03-01","1","defect1","Dyehouse","Aprobado","otherCol1"}},{"Date","Batch#","Defect","Responsable Area","Final Disposition","otherColumn1"}),

   #"QA Report source 2" = Table.FromRows({{"2017-03-02","2","defect2","Dyehouse","not Aprobado","otherCol2"}},{"Date","Batch#","Defect","Responsable Area","Final Disposition","otherColumn2"}), 
 
   #"QA Report source 3" = Table.FromRows({{"2017-03-03","3","defect3","Dyehouse","not Aprobado","otherCol3"}},{"Date","Batch#","Defect","Responsable Area","Final Disposition","otherColumn3"}),

   SelectedColumnsFromSrc1 = Table.SelectColumns(#"QA Report source 1",{"Date","Batch#","Defect","Responsable Area","Final Disposition"}),

   SelectedColumnsFromSrc2 = Table.SelectColumns(#"QA Report source 2",{"Date","Batch#","Defect","Responsable Area","Final Disposition"}),

   SelectedColumnsFromSrc3 = Table.SelectColumns(#"QA Report source 3",{"Date","Batch#","Defect","Responsable Area","Final Disposition"}),

   AppendAll3 = Table.Combine({SelectedColumnsFromSrc1 ,SelectedColumnsFromSrc2, SelectedColumnsFromSrc3 }),

   FilteredData = Table.SelectRows(AppendAll3, each [Responsable Area] = "Dyehouse" and [Final Disposition]<>"Aprobado" ),

   SortFilteredData = Table.Sort(FilteredData,{{"Date",Order.Ascending}} ),

   EarlistFilteredData =Table.FirstN(SortFilteredData , 1)

in
   EarlistFilteredData

[ratercero]

Hello @Eric_Zhang,

Is this how it is supossed to look?

let
#"QA Report source 1" =  PNC_TELA.FromRows({{"2016-01-01","1","0","Dyehouse","Aprobado"}},{"Fecha de Ingreso","Lote","Defecto 1","Proceso Responsable","Disposición"}),

#"QA Report source 2" = PNC_PARTES_CORTADAS.FromRows({{"2016-01-01","1","0","Dyehouse","Aprobado"}},{"Fecha de Ingreso","Lote","Defecto 1","Proceso Responsable","Disposición"}),

#"QA Report source 3" = TONO_MALO.FromRows({{"2016-01-01","1","0","Dyehouse","Aprobado"}},{"Fecha de Ingreso","Lote","Defecto 1","Proceso Responsable","Disposición"}),

SelectedColumnsFromSrc1 = Table.SelectColumns(#"QA Report source 1",{"Fecha de Ingreso","Lote","Defecto 1","Proceso Responsable","Disposición"}),

SelectedColumnsFromSrc2 = Table.SelectColumns(#"QA Report source 2",{"Fecha de Ingreso","Lote","Defecto 1","Proceso Responsable","Disposición"}),

SelectedColumnsFromSrc3 = Table.SelectColumns(#"QA Report source 3",{"Fecha de Ingreso","Lote","Defecto 1","Proceso Responsable","Disposición"}),

AppendAll3 = Table.Combine({SelectedColumnsFromSrc1 ,SelectedColumnsFromSrc2, SelectedColumnsFromSrc3 }),

FilteredData = Table.SelectRows(AppendAll3, each [Proceso Responsable] = "Tintorería" and [Disposición]<>"Aprobado" ),

SortFilteredData = Table.Sort(FilteredData,{{"Fecha de Ingreso",Order.Ascending}} ),

EarlistFilteredData =Table.FirstN(SortFilteredData , 1)

EarlistFilteredData#(lf)"

I'm Getting this error "Expression.SyntaxError: Token Comma expected."

RT

[Phil_Seamark]

Hi @ratercero,

You could merge these in the Query Editor using the Merge feature, or UNION them together in DAX.

If it were me I'd try and get it working in the Query Editor

You can probably do your filtering in the Query Editor too.

[Phil_Seamark]

although you will need to do some shaping of your tables in the Query Editor so the three tables line up (columnwise) for the merge.

Add columns to some tables with blank values, and remove columns from others.