Promoting and changing field names within folders that are actively used

E

Ed Kelly

Member
Joined
Jul 20, 2016
Messages
191
Reaction score
2
Points
18
Excel Version(s)
2016
Am using from file from folder to look at monthly csv downloads from my credit card company. (Ref 1)

Since the csv statements do not contain the statement date I am “manufacturing” it from the monthly file name. and consequently using Csv.Document([content]) and when I double click on the custom column I get to see all appended statement fields within the folder including the “manufactured month” field. I then promote the first row to headers and in this instance change the name of the fist column which now reads “Apr” to “Statement” (ref 2)

For those of you that have nodded off by now here in lies the problem, when I move old files (Statement months csv’s) out of the folder and replace them with newer statement activity power query does its thing and when it looks to promote the first row and change the name of the first column it throws out an error as it cannot find the month “Apr” as the first file in the folder might now actually be Jun or Aug…. (Ref 3)

Is there a way to get PQ over this blip or indeed am I expecting too much from the puppy? (And Totally get that if I was not using a manufactured date field there would not be a problem!)

https://www.screencast.com/t/kEjd918D33
 
Ed,
Have you tried moving "Promoted Headers" step to a position before steps used to create Statement column?
 
Thanks for the reply, cannot see exactly where in the applied steps I might do it earlier, if you can please advise did try to force it to automatically promote by inserting a true in the added custom step however PQ was ok with the syntax however threw out an error

https://www.screencast.com/t/ySKovMoTgwJ
 
I can't copy/paste detail of .png listing Ref #3. Since I'm kinda lazy, would you please post it here. Then I can try to fix it & re-post it.
 
euUbsd36R said:
I can't copy/paste detail of .png listing Ref #3. Since I'm kinda lazy, would you please post it here. Then I can try to fix it & re-post it.
Click to expand...

Thanks for taking the time

let
Source = Folder.Files("C:\Users\eamon\OneDrive\Documents\Credit Cards\MCEK_"),
#"Lowercased Text" = Table.TransformColumns(Source,{{"Extension", Text.Lower}}),
#"Filtered Rows" = Table.SelectRows(#"Lowercased Text", each [Extension] = ".csv"),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",".csv","",Replacer.ReplaceText,{"Name"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","MCEK_","1, ",Replacer.ReplaceText,{"Name"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Name", type date}}),
#"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Name", each Date.MonthName(_), type text}}),
#"Extracted First Characters" = Table.TransformColumns(#"Extracted Month Name", {{"Name", each Text.Start(_, 3), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted First Characters",{{"Name", "Statement Month"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Csv.Document([Content])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true]),
#"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers",{{"Apr", "Statement"}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns1", {{"Transaction Date", type date}, {" Posting Date", type date}}, "en-US"),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type with Locale", {"Transaction Date", " Posting Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Errors",{{" Billing Amount", type number}, {" Merchant", type text}, {" Debit/Credit Flag ", type text}, {" Reference Number ", type text}, {" Merchant Zip ", type text}, {" Merchant State ", type text}, {" Merchant City ", type text}, {" SICMCC Code", Int64.Type}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Transaction Date", "Trans Date"}, {" Billing Amount", "Amount"}, {" Debit/Credit Flag ", "Dr/Cr"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns2", " Merchant", " Merchant - Copy"),
#"Extracted First Characters1" = Table.TransformColumns(#"Duplicated Column", {{" Merchant - Copy", each Text.Start(Text.From(_, "en-CA"), 1), type text}}),
#"Renamed Columns3" = Table.RenameColumns(#"Extracted First Characters1",{{" Merchant - Copy", "Alpha"}})
in
#"Renamed Columns3"
 
Ed,

The error occurs in step #"Renamed Columns1" when a value, "Apr" is used instead of a variable. If you add a step (using advanced editor) just above #"Promoted Headers" which assigns a value to a variable, StmtMoRow0, then use the variable in place of "Apr" in #"Renamed Columns1" as shown below, I think you can eliminate the error. The value assigned to StmtMoRow0 is the value of column, "Statement Month", Row 0 (first row). M syntax for this is Table[Column]{Row}, thus the whole step:
CurrentNameRow0 = #"Expanded Custom"[Statement Month]{0},
where #"Expanded Custom" is the table reference,
[Statement Month] is the column reference,
{0} is a reference to the first row. (M starts all lists at 0, not 1).
Thus in your example #"Expanded Custom"[Statement Month]{0} evaluates to "Apr".

To understand table[]{} syntax, I suggest you peruse Power Query M language specification. (see Section 6.4 Selection and Projection Operators). It's a bit like jumping down the rabbit hole. Discussions are quite terse, but with the help of the several Power Query blogs out there, especially Ken Puls and Chris Webb, it should get you acquainted with M programming. Good luck.


Code: 
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),

    CurrentNameRow0 = #"Expanded Custom"[Statement Month]{0},
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true]),
    #"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers",{{CurrentNameRow0, "Statement"}})
 
euUbsd36R said:
Ed,

The error occurs in step #"Renamed Columns1" when a value, "Apr" is used instead of a variable. If you add a step (using advanced editor) just above #"Promoted Headers" which assigns a value to a variable, StmtMoRow0, then use the variable in place of "Apr" in #"Renamed Columns1" as shown below, I think you can eliminate the error. The value assigned to StmtMoRow0 is the value of column, "Statement Month", Row 0 (first row). M syntax for this is Table[Column]{Row}, thus the whole step:
CurrentNameRow0 = #"Expanded Custom"[Statement Month]{0},
where #"Expanded Custom" is the table reference,
[Statement Month] is the column reference,
{0} is a reference to the first row. (M starts all lists at 0, not 1).
Thus in your example #"Expanded Custom"[Statement Month]{0} evaluates to "Apr".

To understand table[]{} syntax, I suggest you peruse Power Query M language specification. (see Section 6.4 Selection and Projection Operators). It's a bit like jumping down the rabbit hole. Discussions are quite terse, but with the help of the several Power Query blogs out there, especially Ken Puls and Chris Webb, it should get you acquainted with M programming. Good luck.


Code: 
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),

    CurrentNameRow0 = #"Expanded Custom"[Statement Month]{0},
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true]),
    #"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers",{{CurrentNameRow0, "Statement"}})
Click to expand...


Sorry I don't know your first name euUbsd36R However you are a bloody genius! Totally worked. Saved me hours of chasing my tail on this and probably many hundreds of hours going into the future now knowing I can do that on other totally unrelated future projects.

Thank you
Ed
 
You must log in or register to reply here.
Back
Top