(1) PIVOT TABLE NOT UPDATING (2) 2 TABLES PIVOT TABLE

[shah0101]

HI EXPERTS,

I HAVE TWO QUESTIONS:

1) MY TABLE IS UPDATING FINE BUT THE THE NUMBERS ON PIVOT TABLE NOT REFLECTING UPDATE (AFTER REFRESH). ANY SUGGESTIONS PLEASE?

2) ON ANOTHER REPORT I HAVE TWO TABLES OUT WHICH I HAVE CREATED ONE PIVOT TABLE. THERE ARE COUPLE OF CALCULATED FIELDS AS WELL, RELATIONSHIP BETWEEN TABLES LOOKS GOOD (AS ATTACHED IMAGE), REFLECTING BALANCES JUST FINE AS REQUIRED BUT THE PROBLEM IS THAT THE PIVOT TABLE IS NOT BRINGING UP ENTRIES FROM THOSE ENTRIES WHICH ARE NOT YET RELATED IN THE SECOND TABLE. I WANT ALL ENTRIES TO BE IN THE PIVOT TABLE. IS IT POSSIBLE OR AM I ASKING TOO MUCH?

THANKS FOR THE HELP IN ADVANCE.

CHEER!

 

[shah0101]

See if this works for you.
They are dependant on you already having queries called:-

• ES INVOICES 20-21
• 2020-LC

1) Create a blank query and call it > Inv_21_LC_Totals
Go in the query's advanced editor and replace everything that is there with the M Code below.
Load as connection only

let
    Source = #"ES INVOICES  20-21",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"LC NO.", "GROSS VALUE",  "NET VALUE", "IMPORT AMOUNT"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Other Columns",{"IMPORT AMOUNT"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([#"LC NO."] <> " ")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"LC NO."}, {{"Gross Amount", each List.Sum([GROSS VALUE]), type nullable number}, {"Net Amount", each List.Sum([NET VALUE]), type nullable number}})
in
    #"Grouped Rows"

2) Create a 2nd blank query and call it > MergeLCandINV (since it is the final query the name doesn't really matter)
Go in the query's advanced editor and replace everything that is there with the M Code below.
Load as Table > Select where you want the table to go

let
    Source = Table.NestedJoin(#"2020-LC", {"L/C NO."}, #"ES INVOICES  20-21", {"LC NO."}, "ES INVOICES  20-21", JoinKind.LeftOuter),
    #"Expanded ES INVOICES  20-21" = Table.ExpandTableColumn(Source, "ES INVOICES  20-21", {"INVOICE NO.", "GROSS VALUE", "DEPOSITS", "NET VALUE"}, {"INVOICE NO.", "GROSS VALUE", "DEPOSITS", "NET VALUE"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded ES INVOICES  20-21", {"L/C NO."}, Inv_21_LC_Totals, {"LC NO."}, "Inv_21_LC_Totals", JoinKind.LeftOuter),
    #"Expanded Inv_21_LC_Totals" = Table.ExpandTableColumn(#"Merged Queries", "Inv_21_LC_Totals", {"Net Amount"}, {"Net Amount"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Inv_21_LC_Totals",{{"Net Amount", "LC Utilized"}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Renamed Columns", "Subtraction", each [Total Available Value] - [LC Utilized], type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Subtraction",{{"Subtraction", "LC Balance"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"L/C NO.", "BUYER", "AMOUNT", "Total Available Value", "LC Utilized", "LC Balance", "Tolerance", "LC S/Date", "EXPIRY DATE", "INVOICE NO.", "CHINA OFFICE", "GROSS VALUE", "DEPOSITS", "NET VALUE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"LC S/Date", type date}, {"EXPIRY DATE", type date}})
in
    #"Changed Type"

Hi Alex,

This is where I got so far.... Am I going into right direction? What to do next?

 

[shah0101]

View attachment 54841



Hi Alex,

This is where I got so far.... Am I going into right direction? What to do next?

This is the same file I shared with you

 

[Alex Blakenburg]

Please show me the code you are using in both the new queries using the Button for M.
ie go into the advanced editor and copy what is there and paste it here.

 

[shah0101]

Please show me the code you are using in both the new queries using the Button for M.
ie go into the advanced editor and copy what is there and paste it here.

Following is "Inv_21_LC_Totals"
======================
let
Source = #"ES INVOICES 20-21",
#"Removed Other Columns" = Table.SelectColumns(Source,{"LC NO.", "GROSS VALUE", "NET VALUE", "IMPORT AMOUNT"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Other Columns",{"IMPORT AMOUNT"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([#"LC NO."] <> " ")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"LC NO."}, {{"Gross Amount", each List.Sum([GROSS VALUE]), type nullable number}, {"Net Amount", each List.Sum([NET VALUE]), type nullable number}})
in
#"Grouped Rows"




Following is "MergeLCandINV"
======================
let
Source = Table.NestedJoin(#"2020-LC", {"L/C NO."}, #"ES INVOICES 20-21", {"LC NO."}, "ES INVOICES 20-21", JoinKind.LeftOuter),
#"Expanded ES INVOICES 20-21" = Table.ExpandTableColumn(Source, "ES INVOICES 20-21", {"INVOICE NO.", "GROSS VALUE", "DEPOSITS", "NET VALUE"}, {"INVOICE NO.", "GROSS VALUE", "DEPOSITS", "NET VALUE"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded ES INVOICES 20-21", {"L/C NO."}, Inv_21_LC_Totals, {"LC NO."}, "Inv_21_LC_Totals", JoinKind.LeftOuter),
#"Expanded Inv_21_LC_Totals" = Table.ExpandTableColumn(#"Merged Queries", "Inv_21_LC_Totals", {"Net Amount"}, {"Net Amount"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Inv_21_LC_Totals",{{"Net Amount", "LC Utilized"}}),
#"Inserted Subtraction" = Table.AddColumn(#"Renamed Columns", "Subtraction", each [Total Available Value] - [LC Utilized], type number),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Subtraction",{{"Subtraction", "LC Balance"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"L/C NO.", "BUYER", "AMOUNT", "Total Available Value", "LC Utilized", "LC Balance", "Tolerance", "LC S/Date", "EXPIRY DATE", "INVOICE NO.", "CHINA OFFICE", "GROSS VALUE", "DEPOSITS", "NET VALUE"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"LC S/Date", type date}, {"EXPIRY DATE", type date}})
in
#"Changed Type"

 

[Alex Blakenburg]

The applied Steps panel looks really odd. I am expecting it to show the individual steps you can see in the code.
You could try right clicking on your ES INVOICES 20-21 > Selecting Reference, calling the new query Inv_21_LC_Totals, and following the steps you can see in the code to Remove columns (x2), filter rows, group rows. And tell me if that worked.

 

[shah0101]

The applied Steps panel looks really odd. I am expecting it to show the individual steps you can see in the code.
You could try right clicking on your ES INVOICES 20-21 > Selecting Reference, calling the new query Inv_21_LC_Totals, and following the steps you can see in the code to Remove columns (x2), filter rows, group rows. And tell me if that worked.

I am lost now.

On 2nd thought, is it possible to use VLookUp or similar as the data (both Invoice & LC) is already present in the same WorkBook in different WorkSheets?

 

[Alex Blakenburg]

OK I think I have worked out what you did.
It looks like you copied all the code into the formula bar of each of the queries.

You need to go back to my post # 40 and for each of the 2 new queries you have created.

• Open the Home > Advanced Editor
• Select all the code that you see there and replace it with the code for that query from post # 40.

eg for Inv_21_LC_Totals the advanced editor screen should finish up looking like the below (this one load as connection only, since it just feeds into the final Merge Query)

Then repeat for MergeLCandINV but Close and Load that to where you want it in Excel

 

[shah0101]

OK I think I have worked out what you did.
It looks like you copied all the code into the formula bar of each of the queries.

You need to go back to my post # 40 and for each of the 2 new queries you have created.

• Open the Home > Advanced Editor
• Select all the code that you see there and replace it with the code for that query from post # 40.

eg for Inv_21_LC_Totals the advanced editor screen should finish up looking like the below (this one load as connection only, since it just feeds into the final Merge Query)

Then repeat for MergeLCandINV but Close and Load that to where you want it in Excel
View attachment 54890

Dear Alex,

Thanks for your help all the way but its not getting me anywhere so I am now inclined to give it up.

Regards,

 

[Alex Blakenburg]

Thanks for the update. Hopefully you can find another way of doing it.