Search code examples
excelodbcpowerquerym

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

Solution

  • 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