CODEWITHSUNDEEP
excelodbcpowerquerym
Mendizalea
Mendizalea

Reputation: 71

Excel Power Query from ODBC

I have 2 queries, customers and contacts and I want to show the orders of each customer in a sheet in excel as follows:

CUSTOMERS

ID  NAME
1   CLIENT A
2   CLIENT B
3   CLIENT C

CONTACTS

ID  CUSTOMER_ID NAME    PHONE
1   1           NAME 1  999
2   1           NAME 2  000
3   2           NAME 3  888
4   2           NAME 4  333
5   2           NAME 5  111
6   3           NAME 6  777
7   3           NAME 7  555
8   1           NAME 8  444

RESULT

CLIENT A
NAME 1  999
NAME 2  000
NAME 8  444

CLIENT B
NAME 3  888
NAME 4  333
NAME 5  111

CLIENT C
NAME 6  777
NAME 7  555

I don't know much about Excel and I need some guidance on how to do it.

Thanks

Upvotes: 1

Views: 421

Answers (2)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

To accomplish this task using Power Query please follow the steps,

enter image description here

• Select some cell in your Data Table,

• Data Tab => Get&Transform => From Table/Range,

• When the PQ Editor opens: Home => Advanced Editor,

• Make note of all the 2 Tables Names,

• Paste the M Code below in place of what you see.

• And refer the notes

let

    //Source Table CONTACTStbl
    SourceOne = Excel.CurrentWorkbook(){[Name="CONTACTStbl"]}[Content],
    DataTypeOne = Table.TransformColumnTypes(SourceOne,{{"ID", Int64.Type}, {"CUSTOMER_ID", Int64.Type}, {"NAME", type text}, {"PHONE", Int64.Type}}),

    //Source Table CUSTOMERStbl
    SourceTwo = Excel.CurrentWorkbook(){[Name="CUSTOMERStbl"]}[Content],
    DataTypeTwo = Table.TransformColumnTypes(SourceTwo,{{"ID", Int64.Type}, {"NAME", type text}}),

    //Merge both the tables
    Merge = Table.NestedJoin(DataTypeOne, {"CUSTOMER_ID"}, DataTypeTwo, {"ID"}, "CUSTOMERStbl", JoinKind.LeftOuter),
    #"Expanded CUSTOMERStbl" = Table.ExpandTableColumn(Merge, "CUSTOMERStbl", {"ID", "NAME"}, {"ID.1", "NAME.1"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded CUSTOMERStbl",{"NAME", "PHONE", "NAME.1"})
in
    #"Removed Other Columns"

enter image description here

• Change the Table name as RESULTtbl before importing it back into Excel, note that you need to select Only create connection.

enter image description here

• Next open the same query connection RESULTtbl and right click to create a reference (Perform this thrice since 3 CLIENTS), change the query name with the respective CLIENT NAME.

enter image description here

enter image description here

• Filter Client Name and Remove the CLIENT Col

For CLIENT A

let
    Source = RESULTtbl,
    #"Filtered Rows" = Table.SelectRows(Source, each ([NAME.1] = "CLIENT A")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"NAME.1"})
in
    #"Removed Columns"

enter image description here

The above M-Code is for CLIENT A, like wise you need to do for the others. Just copy from the Advance Editor and change the CLIENT NAME there. And import as table in New Worksheet

enter image description here

For CLIENT B

let
    Source = RESULTtbl,
    #"Filtered Rows" = Table.SelectRows(Source, each ([NAME.1] = "CLIENT B")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"NAME.1"})
in
    #"Removed Columns"

enter image description here

For CLIENT C

let
    Source = RESULTtbl,
    #"Filtered Rows" = Table.SelectRows(Source, each ([NAME.1] = "CLIENT C")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"NAME.1"})
in
    #"Removed Columns"

enter image description here

EDIT

With the existing Power Query Connection(RESULTtbl), we can create a Pivot Table, and place the CLIENT field in FILTERS area of Pivot Table, while the NAME & PHONE to be placed in ROWS area.

• Click on Data Tab --> Queries & Connections --> Right Click on RESULTtbl --> Load To --> Pivot Table Report --> Select New Worksheet or Existing Sheet with cell reference --> press Ok.

enter image description here

• Next, from Design Tab under Layout Group turn off the SUBTOTALS (click on Do not Show Subtotals) as well as the GRANDTOTALS (click on Off For Rows & Columns), hide the buttons from Pivot Table Analyze Tab, make sure to select the Report Layout from Design Tab to select the Show in Tabular Form

enter image description here

enter image description here

enter image description here

enter image description here

• Finally run the Show Report Filter Pages -- From Pivot Table Analyze Tab under Options Menu, you will get the desired output, a gif. shown below for your perusal.

enter image description here

Download the workbook from here: CLIENT_WISE_REPORT

Upvotes: 1

Ozan Sen
Ozan Sen

Reputation: 2615

Open The PQ Editor, and Create Your Tables: You can Manually Enter them following : Home --> New Query Group --> Enter Data

T1

T2

Now You need to merge your queries using Merge Queries Function:

Home --> Combine Group --> Merge Queries (See picture)

TTL

Now you need to merge queries based on ID <> CUSTOMER_ID Columns:

See Picture:

DREYR

Then Expand The columns, and you will see a table like this:

Expanded

Then Filter The Name Column to the customer you want to see the result:

Glglgl

Finally Pick the one who load it into your worksheet, and finish task: In Excel You will see a different screen like this:

enter image description here

Click OK and go! Finished!

Upvotes: 2

Related Questions