Excel Power Query from ODBC-CodePudding

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

CodePudding user response：

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

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

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

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

See Picture:

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

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

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

Click OK and go! Finished!

CodePudding user response：

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

• 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"

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

• 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.

• 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"

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

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"

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"