Week Numbers as Headers for new Columns

GreenBoy

New member
Joined
Apr 13, 2015
Messages
26
Reaction score
0
Points
0
Excel Version(s)
Office 365
Hi
This may be terribly easy or not possible - my experience of PQ is so limited I cant say for sure.

I have a report with all the purchase Orders we have been issued by our Client, and dependant on the activity and issue date of the PO, gives us a different period before we are due to recieve the monies in.

For example - for a Survey, PO issued today, we expect the report to be issued, and accepted by the Client in 10 working days - at which time we can invoice. A set of drawings cannot start until after the survey is accepted, so the PO will come out some time around that of the authorisation to Invoice, the drawing can take up to 20 working days and on submission the client then has 5 days to accept, and barring issues, we can bill on that date.... and thus it goes on.

My finance director wants a list of all instructions received, with the value of it to show in the appropriate column on a spreadsheet, so if we are in week 22, then the Survey should be claimable for invoice in week 24 (week 22 plus 10 working days), and then following that the Drawings should be claimable in 25 days beyond that, or 5 weeks making it week 29...

So the data should look something like this:

Start week ----- Activity ----- PO No ------Value ---- Money Due
----------------------------------------------------------- Wk 22 ----- Wk 23 ---- Wk24 ----- Wk25 ----- Wk26 ---- Wk27 ---- Wk28 ----- Wk29 ----- Wk
22 ----------------Survey ------1234 -------£250 ---------------------------------£250
24 ----------------Drawings ----1235 -------£800 ----------------------------------------------------------------------------------------------£800

My Data is currently in columns and I calculate the week due, and I am assuming to get to this arrangement I need to pivot or unpivot the data, but I am not sure how to go about it, and all attempts have come to nothing.

Is this possible - yes/ no

And if so any suggestions , or links to appropriate tutorials appreciate.

Thanks
Dave
 
I personally think this is a job for a few if formulas in excel. Vlookup the activity to get a delay in weeks, add that to the start week, and then use that total number to determine which column to put it in. If you plan to roll the months (so that the week after week 52 goes back to week 1) then modify the total number to roll back to 1 after hitting 52
 
Something along these lines <attached>, set up to roll week 53 into week 1
 

Attachments

  • BillDelay.xlsx
    23.2 KB · Views: 7
Thanks Horseyride - this is something I have already considered, and yes it works just fine.
I am still looking at a way of effectively creating the same solution in PQ - just because i think this would be useful not only for this report but others also.

Thanks Again.
Dave
 
The PQ version attached

Code:
let
    // Queryname = BillDelayTable
    Source = Excel.CurrentWorkbook(){[Name="BillDelayTable"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Activity", type text}, {"Bill Delay", Int64.Type}})
in
    #"Changed Type"

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Week", Int64.Type}, {"Activity", type text}, {"PO#", Int64.Type}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Activity"},BillDelayTable,{"Activity"},"BillDelayTable",JoinKind.LeftOuter),
    #"Expanded BillDelayTable" = Table.ExpandTableColumn(#"Merged Queries", "BillDelayTable", {"Bill Delay"}, {"Bill Delay"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded BillDelayTable", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "DollarWeek", each if ([Start Week]+[Bill Delay]) < 53 then [Start Week]+[Bill Delay] else [Start Week]+[Bill Delay] - 52),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"DollarWeek", Order.Ascending}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Sorted Rows", "Value", "Value - Copy"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Duplicated Column1", {{"DollarWeek", type text}}), List.Distinct(Table.TransformColumnTypes(#"Duplicated Column1", {{"DollarWeek", type text}})[#"DollarWeek"]), "DollarWeek", "Value - Copy"),
    #"NoNull" = Table.ReplaceValue(#"Pivoted Column" ,null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Pivoted Column")),
    #"Sorted Rows1" = Table.Sort(NoNull,{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Bill Delay", "Index"})
in
    #"Removed Columns"
 

Attachments

  • BillDelay2.xlsx
    21.5 KB · Views: 15
That is bloody awesome - thank you.
 
Back
Top