Forum Discussion
Using value from another query in a custom column calculation
Hello PhilC
yes, there is. The problem here is that your external table has to be reloaded on every row and on every step. Use Table.Buffer to buffer your table in your query, so it has only to be loaded once. Use this code instead
BufferedTable = Table.Buffer(#"REPORTING DATE"), #"Added Current Year" = Table.AddColumn(#"Filtered Rows", "Current Year", each Date.Year([Date]) = Date.Year(BufferedTable [REPORTING DATE]{0})), #"Added Previous Year" = Table.AddColumn(#"Added Current Year", "Previous Year", each Date.Year([Date]) = Date.Year(BufferedTable[REPORTING DATE]{0})-1), #"Added Current YTD" = Table.AddColumn(#"Added Previous Year", "Current YTD", each Date.Month([Date]) = Date.Month(BufferedTable [REPORTING DATE]{0}) and [Current Year]), #"Added Previous YTD" = Table.AddColumn(#"Added Current YTD", "Previous YTD", each Date.Month([Date]) = Date.Month(BufferedTable[REPORTING DATE]{0})-1 and [Previous Year]),
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello PhilC
yes, there is. The problem here is that your external table has to be reloaded on every row and on every step. Use Table.Buffer to buffer your table in your query, so it has only to be loaded once. Use this code instead
BufferedTable = Table.Buffer(#"REPORTING DATE"),
#"Added Current Year" = Table.AddColumn(#"Filtered Rows", "Current Year", each Date.Year([Date]) = Date.Year(BufferedTable [REPORTING DATE]{0})),
#"Added Previous Year" = Table.AddColumn(#"Added Current Year", "Previous Year", each Date.Year([Date]) = Date.Year(BufferedTable[REPORTING DATE]{0})-1),
#"Added Current YTD" = Table.AddColumn(#"Added Previous Year", "Current YTD", each Date.Month([Date]) = Date.Month(BufferedTable [REPORTING DATE]{0}) and [Current Year]),
#"Added Previous YTD" = Table.AddColumn(#"Added Current YTD", "Previous YTD", each Date.Month([Date]) = Date.Month(BufferedTable[REPORTING DATE]{0})-1 and [Previous Year]),
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
