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,
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!
@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"
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
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
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
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.
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.
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |