Power query pivot #2

Question

Split from this thread.

Hi

I have 2 more questions (for now...)

#1

Is it possible to get the total of the last number of columns ( 4 or 5 etc ) but not to count the last column in the total( sometimes the report will run in the middle of the week) i want it to show , but should not figure in the total.

#2

Is it possible for the number of columns to be in a parameter based on a cell in a worksheet or some other way where the the user can select the number weeks (columns) that should be totaled ).

at the moment i am using this code that you gave me (very thankful for that) List.Sum(List.LastN(List.Skip(Record.ToList(_),1),3))

I hope i am not a pain....

Thanks

Answer 1

Hi Dave,

here is your modified sample file:
https://www.dropbox.com/scl/fi/wuzju5mkvatcawzn2axo0/867ec747-d378-405e-9e59-fe5f4a3afd82_2.xlsx?rlkey=p15qjuawcqyu9l5cxflyib5gw&dl=1

In there is a Setup sheet with a Parameter table which contains a start date and end date so you can choose the period to be summed up as you like. Special feature: If a date is omitted (empty cell), today's date is used.

The code is similar to my previous sample file, so you can combine both to specify start week and end week instead of dates.

From my point of view, you now have all the options you need. Any questions?

Andreas.

Answer 2

Hi,

This adds up the last 4

let

Source = Excel.CurrentWorkbook(){[Name="SALES"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"ITEM", type text}, {"SALE DATE", type date}, {"SOLD", Int64.Type}}), 

#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"SALE DATE", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"SALE DATE", type text}}, "en-IN")[#"SALE DATE"]), "SALE DATE", "SOLD", List.Sum), 

#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each List.Sum(List.LastN(List.RemoveLastN(Record.ToList(\_),1),4))) 

in

#"Added Custom"

Hope this helps.

Answer 3

Hi,

Not clear about your request. Share some data and show the expected result.

Answer 4

Hi Anreas

Actually this data comes from an unpivoted file

As i mentioned in my original post this was done with power query grouping and power q pivot.

Regular pivot is not an option for numerus reasons there will be some manual data entry in excel after power q is loaded back to excel and going back to power q . and other reasons

Thanks

Answer 5

Dave,

Why do you want all this in Power Query?

If your focus is a week based analysis, why not unpivot all the date columns, associate a calendar, load all data into the Data Model and create a Pivot table to aggregate the values.

Please show us an example file, so we can make you a meaningful sample file.

Andreas.