Looking for a Power Query solution to group dates into mixed month billing period

daleholden

Board Regular
Joined
Sep 22, 2002
Messages
243
[FONT=&quot]Hello Cannot seem to find an answer on here unless i am poor at searching. I bring my data into PQuery clean it and send it back to Excel. I then run a PVTable but like all Credit Cards they run between two dates. Mine is 20th to 19th of next month. I am trying to show in a PVTable a table showing period. I have a solution in Excel vis this post https://www.reddit.com/r/excel/comm...ower_pivot_mixed_dates_over_12_month/dpsvytf/[/FONT]
[FONT=&quot]But i am already doing a lot of work in Power Query so i would like to do it if possible. I would like to add a column and get it to look at the dates and group it as Credit Card period 20th Sept to 19 Sept etc etc[/FONT]
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
You should add column to your table ( in PQ of course)
Code:
= Table.AddColumn(#"Changed Type", "Period", each if Date.Day([Date]) >= 20 then "20 "& Date.ToText([Date],"MMM","en-US") & " - 19 " & Date.ToText([Date]+#duration(30,0,0,0), "MMM", "en-US") else "20 "& Date.ToText([Date]-#duration(30,0,0,0),"MMM","en-US") & " - 19 " & Date.ToText([Date], "MMM", "en-US") , type text )
 
Upvote 0
You should add column to your table ( in PQ of course)
Code:
= Table.AddColumn(#"Changed Type", "Period", each if Date.Day([Date]) >= 20 then "20 "& Date.ToText([Date],"MMM","en-US") & " - 19 " & Date.ToText([Date]+#duration(30,0,0,0), "MMM", "en-US") else "20 "& Date.ToText([Date]-#duration(30,0,0,0),"MMM","en-US") & " - 19 " & Date.ToText([Date], "MMM", "en-US") , type text )


Hello Bill



Cheers for reply when i add the code i am only getting a column with a table in it?

Also I use UK date format i have tried changing the US to UK but whatever do i get an error when I expand the table and just select Period
See here https://1drv.ms/i/s!Ao6WCX2miTCJhrRWVnDzVfCuXDGC9Q
 
Last edited:
Upvote 0
Change your last step to
Code:
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Period", each if Date.Day([Date]) >= 20 then "20 "& Date.ToText([Date],"MMM","en-US") & " - 19 " & Date.ToText([Date]+#duration(30,0,0,0), "MMM", "en-US") else "20 "& Date.ToText([Date]-#duration(30,0,0,0),"MMM","en-US") & " - 19 " & Date.ToText([Date], "MMM", "en-US") , type text )

You can change "en-US" to "en-GB" but in this case it doesn't matter.
 
Upvote 0
HI Bill

We have an issue i have 341 rows of data when i add your code it works BUT its goes to 116,000 rows massive duplication????

let
DaleLaptop = "D:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\Amex Statement",
DalePC1 = "G:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\Amex Statement",


LaptopTest = try List.Count(Folder.Files(DaleLaptop)[Content]),
Desktop1Test = try List.Count(Folder.Files(DalePC1)[Content]),


Source = if LaptopTest[HasError] then
if Desktop1Test[HasError] then

"Cannot find Data Source"


else
Folder.Files(DalePC1)
else
Folder.Files(DaleLaptop),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Invoke Custom Function1" = Table.AddColumn(#"Removed Other Columns", "Transform File from Amex Statement", each #"Transform File from Amex Statement"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Amex Statement"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Amex Statement", Table.ColumnNames(#"Transform File from Amex Statement"(#"Sample File (4)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", type date}, {"Column2", type text}, {"Column3", type number}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Column3", "Price"}, {"Column4", "Payee"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Column6", "Column5"}),
#"Capitalized Each Word" = Table.TransformColumns(#"Removed Columns1",{{"Payee", Text.Proper, type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Capitalized Each Word",{{"Price", Currency.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","Uk","UK",Replacer.ReplaceText,{"Payee"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Table.AddColumn(#"Replaced Value", "Period", each if Date.Day([Date]) >= 20 then "20 "& Date.ToText([Date],"MMM","en-US") & " - 19 " & Date.ToText([Date]+#duration(30,0,0,0), "MMM", "en-US") else "20 "& Date.ToText([Date]-#duration(30,0,0,0),"MMM","en-US") & " - 19 " & Date.ToText([Date], "MMM", "en-US") , type text )),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Period"}, {"Period"})
in
#"Expanded Custom"
 
Upvote 0
HI Bill

Got it working had to click in one cell on the table link it then opened it correctly if i click the double arrows it adds all those duplicate records
 
Upvote 0
Hi Dale,
You did not do what I asked. Your step is:
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Table.AddColumn(#"Replaced Value", "Period", each if Date.Day([Date]) >= 20 then "20 "& Date.ToText([Date],"MMM","en-US") & " - 19 " & Date.ToText([Date]+#duration(30,0,0,0), "MMM", "en-US") else "20 "& Date.ToText([Date]-#duration(30,0,0,0),"MMM","en-US") & " - 19 " & Date.ToText([Date], "MMM", "en-US") , type text ))

You should remove the red part an also remove the #"Expanded Custom" step.
Cheers
 
Upvote 0
HI Bill

Looking good now cheers for the help. One last thing the figures are not equal to my statements. So i guess that is because they previous data i do not have has a carry over balance any idea on how i can add something to make these periods match my actual statements?
 
Upvote 0
I do not understand what you want to achieve.
Probably becouse of my english.
Can you share somwhere a small example file with explanations what and where you want to get?
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top