CODEWITHSUNDEEP
excelpowerquery
Mendizalea
Mendizalea

Reputation: 71

Excel Power Query from ODBC with 3 tables

I have 3 queries, WORKS, MATERIALS and TOOLS and I want to show the RESULT of each WORK in a sheet in excel as follows:

WORKS

ID  DATE        DESCRIPTION TIME
1   31/10/2022  XXXXXXXX1   2:00
2   31/10/2022  XXXXXXXX2   4:00
3   31/10/2022  XXXXXXXX3   1:00

MATERIALS

ID  WORK_ID NAME    QUANTITY
1   1       SCREW   20
2   1       PAINT   1
3   2       PAINT   2
4   2       STAPLE  40
5   3       PAINT   1
6   1       STAPLE  30
7   3       SCREW   5
8   2       SCREW   1

TOOLS

ID  WORK_ID NAME    TIME
1   1       STAPLER 1:00
2   2       STAPLER 2:00

RESULT

1   31/10/2022  XXXXXXXX1   2:00
                SCREW       20
                PAINT       1
                STAPLE      30
                STAPLER     1:00
2   31/10/2022  XXXXXXXX2   4:00
                PAINT       2
                STAPLE      40
                SCREW       1
                STAPLER     2:00
3   31/10/2022  XXXXXXXX3   1:00
                PAINT       1
                SCREW       5

the result is a sample of what I need, the design may be different but with the information shown in the example.

Example Excel: https://1drv.ms/x/s!AnYimXo7OdOygfNLd_hi-ZI-BuUvBw?e=HHIBXA

The problem is that in the pivot table it shows me not only the values of each work.

Thanks

Upvotes: 0

Views: 67

Answers (1)

horseyride
horseyride

Reputation: 21318

In powerquery, change some column names so they stack better, then combine the tables and sort them using code similar to:

let combined = Table.AddIndexColumn(works, "Index", 0, 1, Int64.Type) 
    & Table.AddIndexColumn(materials, "Index", 9999, 1, Int64.Type) 
    & Table.AddIndexColumn(tools, "Index", 99999, 1, Int64.Type),
#"Sorted Rows" = Table.Sort(combined,{{"ID", Order.Ascending}, {"Index", Order.Ascending}})
in combined

Upvotes: 3

Related Questions