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

View attachment 54375

This is what it looks like now!

 

[Alex Blakenburg]

This was such a long time ago.
As I recollect you previously had 2 queries.

• INVOICES - 21-22
  and
• 2021-LC

I seem to recollect that my 2 queries followed on from that.
Do you still have those queries ? Can you get to that point ?

 

[shah0101]

This was such a long time ago.
As I recollect you previously had 2 queries.

• INVOICES - 21-22
  and
• 2021-LC

I seem to recollect that my 2 queries followed on from that.
Do you still have those queries ? Can you get to that point ?

Yes, same query, allow me to explain it:

Invoices - 21-22: is one folder where hundreds of invoices (workbooks>worksheets) are located

2021-LC: is one sheet from a workbook


There are number of LC numbers in 2021-LC with their values. Those LC numbers may or may not relate to one or multiple Invoices present in Invoices - 21-22.

The task is to relate those LC numbers from 2021-LC to Invoices - 21-22. If related invoices found then it must deduct the invoice value from the related LC in a new calculated field. Related Invoice Numbers, Related Invoice Values must be shown on the report to find out which invoices were deducted from any LC in discussion.

Yesterday I have uploaded number of images to where I have got so far in the same thread above.

Thanks.

 

[shah0101]

Yes, same query, allow me to explain it:

Invoices - 21-22: is one folder where hundreds of invoices (workbooks>worksheets) are located

2021-LC: is one sheet from a workbook


There are number of LC numbers in 2021-LC with their values. Those LC numbers may or may not relate to one or multiple Invoices present in Invoices - 21-22.

The task is to relate those LC numbers from 2021-LC to Invoices - 21-22. If related invoices found then it must deduct the invoice value from the related LC in a new calculated field. Related Invoice Numbers, Related Invoice Values must be shown on the report to find out which invoices were deducted from any LC in discussion.

Yesterday I have uploaded number of images to where I have got so far in the same thread above.

Thanks.

I uploaded the images because the video was not serving the purpose. Hope these images helps.

 

[shah0101]

I uploaded the images because the video was not serving the purpose. Hope these images helps.

I need the report in somewhat this format.....

I am already using that but the basic problem with this is it doesn't bring up those LCs against which it can not find entries in Invoices.

 

[Alex Blakenburg]

You previously had these 2 queries. What happened to them ?

 

[shah0101]

You previously had these 2 queries. What happened to them ?

View attachment 54446

To fix it I made numerous queries and excel sheets, I can still change the name, i guess. Following is the current workbook on which I made connection only and tried to add the second query and this is what it looks now:

 

[Alex Blakenburg]

I am not sure that I am going to be able to help you.
Without access to your data I was relying on you already having

• a query to import the invoice data from the folder and
• a query to import the LC data.

 

[shah0101]

I am not sure that I am going to be able to help you.
Without access to your data I was relying on you already having

• a query to import the invoice data from the folder and
• a query to import the LC data.

Okay, I am sharing the file privately as am not comfortable sharing the data openly.

 

[Alex Blakenburg]

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"