I am sourcing a data file in PowerBI, which contains text in all columns. I want to prepend additional text, which I've stored in a variable, to all column contents except for the first column. I am able to get the column names, but am stuck figuring out how to iterate over the names (e.g. transform each with "http://my.site/" & [column_data]).
let
Source = Excel.Workbook(File.Contents("C:\results.xlsx"), true, true),
results_Sheet = Source{[Item="results",Kind="Sheet"]}[Data],
results_columns = Table.ColumnNames(results_Sheet),
suffixes = List.RemoveItems(results_columns, {"left_column_name"})
prefix = my_prefix_variable
final_result = ???
in
final_result
You have gone to List what prevents you to do further editing. Keep working with a table.. I would go with following: Keep first row (before making it a header), transform to Rows and add a column with a prefix:
let
Source = Excel.Workbook(File.Contents("C:\results.xlsx"), true, true),
results_Sheet = Source{[Item="results",Kind="Sheet"]}[Data],
#"Kept First Rows" = Table.FirstN(Source,1),
#"Transposed Table" = Table.Transpose(#"Kept First Rows"),
#"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each myPara &[Column1])
in
#"Added Custom"