Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Ashley Madison transformation
let
Source = Csv.Document(File.Contents("E:\Ashley\CSV\Transactions.csv"),[Delimiter=",",Encoding=1252]),
#"Promoted Headers" = Table.PromoteHeaders(Source),
#"Kept First Rows" = Table.FirstN(#"Promoted Headers",100000),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"AMOUNT", "BRAND", "CARD ENDING", "FIRST NAME", "DATE", "ERROR CODE", "TYPE", "CITY", "COUNTRY", "STATE", "ZIP"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"AMOUNT", type number},{"BRAND", type text}, {"CARD ENDING", Int64.Type},{"FIRST NAME", Int64.Type}, {"DATE", type datetime},{"ERROR CODE", Int64.Type}, {"TYPE", type text}, {"CITY", type text}, {"COUNTRY", type text}, {"STATE", type text}, {"ZIP", type text}}),
#"Remove Error Rows" = Table.SelectRows(#"Changed Type", each [ERROR CODE] is null),
#"Removed Error Columns" = Table.RemoveColumns(#"Remove Error Rows",{"ERROR CODE"}),
#"Add DateTime Column" = Table.RenameColumns (#"Removed Error Columns",{{"DATE", "DateTime"}}),
#"Add Date Column" = Table.TransformColumnTypes(Table.DuplicateColumn(#"Add DateTime Column", "DateTime", "Date"),{{"Date", type date}}),
#"Add Year Column" = Table.AddColumn(#"Add Date Column", "Year", each Date.Year([#"Date"]), Int64.Type),
#"Add Qtr Column" = Table.AddColumn(#"Add Year Column", "Qtr", each Date.QuarterOfYear([#"Date"]), type number),
#"Add Value Column" = Table.AddColumn(#"Add Qtr Column", "Value",
each (if [BRAND]="null" then 0 else
if [TYPE]="Settlements" then [AMOUNT] else
if [TYPE]="Purchases" then [AMOUNT] else
if [TYPE]="Credits" then Number.Abs(0-[AMOUNT]) else
if [TYPE]="Chargebacks" then Number.Abs(0-[AMOUNT]) else 0), type number),
#"Add User Column" = Table.AddColumn(#"Add Value Column", "User", each Text.Combine({Text.From([CARD ENDING]*[FIRST NAME]),[ZIP]},"")),
#"Remove Unused Columns" = Table.RemoveColumns(#"Add User Column",{"BRAND", "CARD ENDING", "FIRST NAME", "DateTime", "TYPE"}),
#"Renamed Columns" = Table.RenameColumns(#"Remove Unused Columns",{{"CITY", "City"}, {"COUNTRY", "CountryCode"}, {"STATE", "State"}, {"ZIP", "ZipCode"}})
in
#"Renamed Columns"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment