Share via

Advanced Editor coding replacing above custom column instead of adding a new column to the sheet.

Doug W 21 Reputation points
2020-12-11T17:10:09.697+00:00

Hello,

Beginner using advanced editor, below is my code, the last set of Custom Columns I entered in manually, the columns are doing what I want in the applied steps, however my intention was for all of them to be seperate columns in the end, however my code is replacing one columns contents in each step so I only end up with customcolumn12. Thanks for any and all help.
let
Source = Excel.Workbook(File.Contents("M:\AccountngNew\Accounting\Accting functionality\daily_position.xlsx"), null, true),
#"Daily Positions_Sheet" = Source{[Item="Daily Positions",Kind="Sheet"]}[Data],
#"Added Custom" = Table.AddColumn(#"Daily Positions_Sheet", "Custom", each if [Column1]="MACQ" then [Column1] else null),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Custom", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Broker"}}),
#"Filled Down" = Table.FillDown(#"Renamed Columns",{"Broker"}),
#"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Column2", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Trade Date", each if Text.Start([Column2],1)="(" then [Column2] else null),
#"Filled Down1" = Table.FillDown(#"Added Custom1",{"Trade Date"}),
#"Renamed Columns1" = Table.RenameColumns(#"Filled Down1",{{"Column1", "Spot Month"}, {"Column2", "Lots"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Column3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Column36", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type", "Broker4", each if Text.Start([Column7],3)="N08" then [Column7] else null),
#"Added Custom3" = Table.AddColumn(#"Daily Positions_Sheet", "Broker1", each if Text.Start([Column10],4)="SPEC" then [Column10] else null),
#"Added Custom4" = Table.AddColumn(#"Daily Positions_Sheet", "Broker2", each if Text.Start([Column13],3)="N14" then [Column13] else null),
#"Added Custom5" = Table.AddColumn(#"Daily Positions_Sheet", "Broker3", each if Text.Start([Column16],3)="N14" then [Column16] else null),
#"Added Custom6" = Table.AddColumn(#"Daily Positions_Sheet", "Broker5", each if Text.Start([Column19],3)="N22" then [Column19] else null),
#"Added Custom7" = Table.AddColumn(#"Daily Positions_Sheet", "Broker6", each if Text.Start([Column22],3)="N22" then [Column22] else null),
#"Added Custom8" = Table.AddColumn(#"Daily Positions_Sheet", "Broker7", each if Text.Start([Column25],3)="MRX" then [Column25] else null),
#"Added Custom9" = Table.AddColumn(#"Daily Positions_Sheet", "Broker8", each if Text.Start([Column28],3)="MRX" then [Column28] else null),
#"Added Custom10" = Table.AddColumn(#"Daily Positions_Sheet", "Broker9", each if Text.Start([Column31],3)="SUC" then [Column31] else null),
#"Added Custom11" = Table.AddColumn(#"Changed Type", "Trade Date2", each if Text.Start([Column36],1)="(" then [Column36] else null),
#"Added Custom12" = Table.AddColumn(#"Daily Positions_Sheet", "broker10", each if Text.Start([Column4],3)="N08" then [Column4] else null)

in
#"Added Custom12"

Community Center Not monitored
0 comments
Accepted answer
  1. Lz._ 9,016 Reputation points
    2020-12-11T17:49:16.097+00:00

    Hi @Doug W

    Your problem starts at: #"Added Custom2" where you reference table #"Changed Type" instead of #"Changed Type1". And later you always referrence to the same table (#"Daily Positions_Sheet"), except at step #"Added Custom11" where you ref. again table #"Changed Type"

    Hoping I haven't made a mistake in copying/pasting your references (step names) you should be good with: 

    let  
    Source = Excel.Workbook(File.Contents("M:\AccountngNew\Accounting\Accting functionality\daily_position.xlsx"), null, true),  
    #"Daily Positions_Sheet" = Source{[Item="Daily Positions",Kind="Sheet"]}[Data],  
    #"Added Custom" = Table.AddColumn(#"Daily Positions_Sheet", "Custom", each if [Column1]="MACQ" then [Column1] else null),  
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Custom", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93"}),  
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93"}),  
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Broker"}}),  
    #"Filled Down" = Table.FillDown(#"Renamed Columns",{"Broker"}),  
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Column2", type text}}),  
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Trade Date", each if Text.Start([Column2],1)="(" then [Column2] else null),  
    #"Filled Down1" = Table.FillDown(#"Added Custom1",{"Trade Date"}),  
    #"Renamed Columns1" = Table.RenameColumns(#"Filled Down1",{{"Column1", "Spot Month"}, {"Column2", "Lots"}}),  
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Column3"}),  
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Column36", type text}}),  
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Broker4", each if Text.Start([Column7],3)="N08" then [Column7] else null),  
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Broker1", each if Text.Start([Column10],4)="SPEC" then [Column10] else null),  
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Broker2", each if Text.Start([Column13],3)="N14" then [Column13] else null),  
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Broker3", each if Text.Start([Column16],3)="N14" then [Column16] else null),  
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Broker5", each if Text.Start([Column19],3)="N22" then [Column19] else null),  
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Broker6", each if Text.Start([Column22],3)="N22" then [Column22] else null),  
    #"Added Custom8" = Table.AddColumn(#"Added Custom7", "Broker7", each if Text.Start([Column25],3)="MRX" then [Column25] else null),  
    #"Added Custom9" = Table.AddColumn(#"Added Custom8", "Broker8", each if Text.Start([Column28],3)="MRX" then [Column28] else null),  
    #"Added Custom10" = Table.AddColumn(#"Added Custom9", "Broker9", each if Text.Start([Column31],3)="SUC" then [Column31] else null),  
    #"Added Custom11" = Table.AddColumn(#"Added Custom10", "Trade Date2", each if Text.Start([Column36],1)="(" then [Column36] else null),  
    #"Added Custom12" = Table.AddColumn(#"Added Custom11", "broker10", each if Text.Start([Column4],3)="N08" then [Column4] else null)  
in  
    #"Added Custom12"

    If this solves your problem please mark this post as answer to help others with a similar challenge. Thanks & Nice day...

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Doug W 21 Reputation points
    2020-12-11T17:51:50.113+00:00

    Amazing thank you so much for your help, worked perfectly.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.