Add Ascending Number in Power Query

[daleholden]

HI Guys

Sorry if this is a newbie question adding a Custom Column in PQ and i want it to add GEN100 then increase by 1 in each row so next down is GEN101 etc etc. Is this possible i know i can do it in excel but if i go back into PQ it loses that excel work.

How do you do it or is their a special formula i need to add.

Dale

 

[billszysz]

Hi dale
If you want one step only, it will be something like this (M language knowledge is needed)

YourStep = Table.TransformColumns(Table.AddIndexColumn(YourPreviousStep, "YourNewColumnName", 100, 1), {"YourNewColumnName", each "GEN"&Text.From(_)})

If you want to use UI only it will be more steps.

AddIndexCol = Table.AddIndexColumn(YourPreviousStep, "Index", 100, 1),
    GENColumn = Table.AddColumn(AddIndexCol, "YourText", each "GEN"),
    ChangeType = Table.TransformColumnTypes(GENColumn,{{"Index", type text}, {"YourText", type text}}),
    MergeColumns = Table.CombineColumns(ChangeType,{"YourText", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"YourColumn")

Regards

 

[daleholden]

Ni Bill

Tried it getting an error see here

Dale

 

[billszysz]

You can't do that in this way
The line with a step you should add in advanced editor.
Maybe put your code here and I will try to change it.

 

[billszysz]

I've prepared a sample file with these two techniques.
Try and analyze if you want
https://drive.google.com/file/d/0B6UlMk8OzUrxVDNkZFd3bXZMTXc/view?usp=sharing

 

[daleholden]

Hi Bill

Code is here i also need to format date different so it looks like dd/mm/yyyy is this possible?

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Flag", type any}, {"Check Number", type any}, {"Date", type datetime}, {"Payee", type text}, {"Category", type text}, {"Master Category", type text}, {"Sub Category", type text}, {"Memo", type any}, {"Outflow", type number}, {"Inflow", type number}, {"Cleared", type text}, {"Running Balance", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Flag", "Check Number"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Account", "Line 1 Account"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Line 1 Account] = "Costco Amex ####3000" or [Line 1 Account] = "M&S Mastercard ####0615")),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each Date.IsInPreviousMonth([Date])),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Payee] <> "Costco Online" and [Payee] <> "Transfer : First Direct ####2378")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Type", each "General Transaction"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Type", "Date", "Line 1 Account", "Payee", "Category", "Master Category", "Sub Category", "Memo", "Outflow", "Inflow", "Cleared", "Running Balance"}),
#"Added Custom1" = Table.AddColumn(#"Reordered Columns", "ID", each "GEN344"),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Type", "Date", "ID", "Line 1 Account", "Payee", "Category", "Master Category", "Sub Category", "Memo", "Outflow", "Inflow", "Cleared", "Running Balance"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Memo"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Payee", "Memo"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "Lines", each 2),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Lines", Int64.Type}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Changed Type2",{"Type", "Date", "ID", "Line 1 Account", "Memo", "Lines", "Category", "Master Category", "Sub Category", "Outflow", "Inflow", "Cleared", "Running Balance"}),
#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Category", "Master Category", "Sub Category", "Running Balance"}),
#"Reordered Columns3" = Table.ReorderColumns(#"Removed Columns2",{"Type", "Date", "ID", "Memo", "Lines", "Line 1 Account", "Outflow", "Inflow", "Cleared"}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns3","Costco Amex ####3000","Amex Costco",Replacer.ReplaceText,{"Line 1 Account"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","M&S Mastercard ####0615","Marks & Spencer Mastercard",Replacer.ReplaceText,{"Line 1 Account"}),
#"Removed Columns3" = Table.RemoveColumns(#"Replaced Value1",{"Cleared"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns3",{{"Outflow", "Line 1 Amount"}}),
#"Filtered Rows3" = Table.SelectRows(#"Renamed Columns2", each true),
#"Renamed Columns3" = Table.RenameColumns(#"Filtered Rows3",{{"Line 1 Amount", "Line 2 Amount"}, {"Inflow", "Line 1 Amount"}}),
#"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns3",367.79,-367.79,Replacer.ReplaceValue,{"Line 1 Amount"}),
#"Added Custom3" = Table.AddColumn(#"Replaced Value2", "Lines 2 Account", each "Owner's Drawings and Personal Use"),
#"Reordered Columns4" = Table.ReorderColumns(#"Added Custom3",{"Type", "Date", "ID", "Memo", "Lines", "Line 1 Account", "Line 1 Amount", "Lines 2 Account", "Line 2 Amount"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Reordered Columns4",{{"Type", type text}, {"ID", type text}, {"Memo", type text}, {"Line 1 Account", type text}, {"Lines 2 Account", type text}, {"Line 2 Amount", Currency.Type}, {"Line 1 Amount", Currency.Type}, {"Date", type date}})
in
#"Changed Type3"

 

[daleholden]

Hi Bill

Code is here i also need to format date different so it looks like dd/mm/yyyy is this possible?
Finally is it possible for column Line 1 Amount to have same numbers as Line 2 Amount but with a negative value?


let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Flag", type any}, {"Check Number", type any}, {"Date", type datetime}, {"Payee", type text}, {"Category", type text}, {"Master Category", type text}, {"Sub Category", type text}, {"Memo", type any}, {"Outflow", type number}, {"Inflow", type number}, {"Cleared", type text}, {"Running Balance", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Flag", "Check Number"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Account", "Line 1 Account"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Line 1 Account] = "Costco Amex ####3000" or [Line 1 Account] = "M&S Mastercard ####0615")),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each Date.IsInPreviousMonth([Date])),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Payee] <> "Costco Online" and [Payee] <> "Transfer : First Direct ####2378")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Type", each "General Transaction"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Type", "Date", "Line 1 Account", "Payee", "Category", "Master Category", "Sub Category", "Memo", "Outflow", "Inflow", "Cleared", "Running Balance"}),
#"Added Custom1" = Table.AddColumn(#"Reordered Columns", "ID", each "GEN344"),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Type", "Date", "ID", "Line 1 Account", "Payee", "Category", "Master Category", "Sub Category", "Memo", "Outflow", "Inflow", "Cleared", "Running Balance"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Memo"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Payee", "Memo"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "Lines", each 2),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Lines", Int64.Type}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Changed Type2",{"Type", "Date", "ID", "Line 1 Account", "Memo", "Lines", "Category", "Master Category", "Sub Category", "Outflow", "Inflow", "Cleared", "Running Balance"}),
#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Category", "Master Category", "Sub Category", "Running Balance"}),
#"Reordered Columns3" = Table.ReorderColumns(#"Removed Columns2",{"Type", "Date", "ID", "Memo", "Lines", "Line 1 Account", "Outflow", "Inflow", "Cleared"}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns3","Costco Amex ####3000","Amex Costco",Replacer.ReplaceText,{"Line 1 Account"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","M&S Mastercard ####0615","Marks & Spencer Mastercard",Replacer.ReplaceText,{"Line 1 Account"}),
#"Removed Columns3" = Table.RemoveColumns(#"Replaced Value1",{"Cleared"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns3",{{"Outflow", "Line 1 Amount"}}),
#"Filtered Rows3" = Table.SelectRows(#"Renamed Columns2", each true),
#"Renamed Columns3" = Table.RenameColumns(#"Filtered Rows3",{{"Line 1 Amount", "Line 2 Amount"}, {"Inflow", "Line 1 Amount"}}),
#"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns3",367.79,-367.79,Replacer.ReplaceValue,{"Line 1 Amount"}),
#"Added Custom3" = Table.AddColumn(#"Replaced Value2", "Lines 2 Account", each "Owner's Drawings and Personal Use"),
#"Reordered Columns4" = Table.ReorderColumns(#"Added Custom3",{"Type", "Date", "ID", "Memo", "Lines", "Line 1 Account", "Line 1 Amount", "Lines 2 Account", "Line 2 Amount"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Reordered Columns4",{{"Type", type text}, {"ID", type text}, {"Memo", type text}, {"Line 1 Account", type text}, {"Lines 2 Account", type text}, {"Line 2 Amount", Currency.Type}, {"Line 1 Amount", Currency.Type}}),
#"Renamed Columns4" = Table.RenameColumns(#"Changed Type3",{{"Line 1 Account", "Line 2 Account"}, {"Lines 2 Account", "Lines 1 Account"}}),
#"Reordered Columns5" = Table.ReorderColumns(#"Renamed Columns4",{"Type", "Date", "ID", "Memo", "Lines", "Lines 1 Account", "Line 1 Amount", "Line 2 Account", "Line 2 Amount"})
in
#"Reordered Columns5"