(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!

 

[Alex Blakenburg]

I would need data to work with, some sample rows from both LC_21 and INV_21 or if you want to share a desensitised copy of your workbook via a file sharing platform such as dropbox, onedrive, google drive etc.

 

[Alex Blakenburg]

Try this:
In Power Query
1) Qry - Inv_21_LC_Totals

• Create a New Blank Query
• Call it > Inv_21_LC_Totals
• Open the Advanced Editor and copy in the code below
• Close and Load To > Connection Only

let
    Source = #"INVOICES - 21-22",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"LC NO.", "GROSS VALUE",  "NET VALUE", "IMPORT AMOUNT", "VALUE", "BOC VAL"}),
    #"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}, {"Amount", each List.Sum([VALUE]), type nullable number}, {"BOC Amount", each List.Sum([BOC VAL]), type nullable number}})
in
    #"Grouped Rows"

2) Qry - MergeLCandINV

• Create a 2nd New Blank Query
• Call it > MergeLCandINV
• Open the Advanced Editor and copy in the code below
• Close and Load To > Select either a new worksheet or an existing one

let
    Source = Table.NestedJoin(#"2021-LC", {"L/C NO."}, #"INVOICES - 21-22", {"LC NO."}, "Inv_21", JoinKind.LeftOuter),
    #"Expanded Inv_21" = Table.ExpandTableColumn(Source, #"INVOICES - 21-22", {"INVOICE NO.", "GROSS VALUE", "DEPOSITS", "NET VALUE"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Inv_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"

Let me know if it works and / or where you get up to with it.

 

[Alex Blakenburg]

Oops replace the code for item 2 above with this:
2) Qry - MergeLCandINV

let
    Source = Table.NestedJoin(#"2021-LC", {"L/C NO."}, #"INVOICES - 21-22", {"LC NO."}, "INVOICES - 21-22", JoinKind.LeftOuter),
    #"Expanded Inv_21" = Table.ExpandTableColumn(Source, "INVOICES - 21-22", {"INVOICE NO.", "GROSS VALUE", "DEPOSITS", "NET VALUE"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Inv_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"

 

[shah0101]

Dear Alex,

First, I must apologize that you wrote script for me and I didn't even replied you back, I am really Sorry.....

Will go through and try to implement the script and will revert with outcome.

Rgds,

 

[Alex Blakenburg]

All good. Let me know how you go.

 

[shah0101]

Hi Alex,

I think I am not starting in the direction...... I uploaded this video, can you please have a look and guide me where to start please?

Book1 - Excel 2022-01-04 10-10-47.mp4

drive.google.com

Please guide / advise.

 

[Alex Blakenburg]

The video indicates that when you click on GetData nothing comes up. Is that correct ?
Your profile does not say what version of Excel you are running. Can you update your profile and let me know which version.

 

[shah0101]

The video indicates that when you click on GetData nothing comes up. Is that correct ?
Your profile does not say what version of Excel you are running. Can you update your profile and let me know which version.

Excel is 2019.

I think I am unable to capture the rest of the actions on the video.

I'll to explain it here manually:

For: "INVOICES - 21-22"
Data > Get Data > From Folder> Combine and Transform Data

Then "Power Query Editor" opens with data.


What to do next please?

 

[shah0101]

Excel is 2019.

I think I am unable to capture the rest of the actions on the video.

I'll to explain it here manually:

For: "INVOICES - 21-22"
Data > Get Data > From Folder> Combine and Transform Data

Then "Power Query Editor" opens with data.


What to do next please?

 

[shah0101]

View attachment 54375

"Power Query Editor" opens with data and "Close & Load To....

Renamed Querry: