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

[daleholden]

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

 

[billszysz]

You should add column to your table ( in PQ of course)

= 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 )

 

[daleholden]

You should add column to your table ( in PQ of course)

= 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

 

[billszysz]

Change your last step to

    #"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.

 

[daleholden]

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"

 

[daleholden]

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

 

[billszysz]

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

 

[daleholden]

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?

 

[billszysz]

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?

 

[daleholden]

HI Bill

Hope this helps here