I have a program which records and outputs values to a csv file, however the data is difficult to organise so i'm looking for a way of transforming the data into a more typical table format. I was thinking that power query could be the way to do it but not sure how to go about it.
I've attached two file, one is the "PRE" which is how it is output from the program, with data added in pairs per line.
The other is the "FINAL" which is how I would like the data to be arranged.
If anyone has any advice on how I could do it that would be greatly appreciated.
Maybe something like this:
let
Source = Excel.Workbook(File.Contents("C:path to your file herePRE.xlsx"), null, true),
PRE_Sheet = Source{[Item="PRE",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(PRE_Sheet, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "FieldList", each List.Range(Record.ToList(_), 5)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "signal_back_price", each try List.Range([FieldList], List.PositionOf([FieldList], "signal_back_price"), 2){1} otherwise null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"FieldList"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Time", "Market", "Automation instance", "Section type:Event/Market/{Selection Name}", "Count of name/value pairs in this Section before next Section column", "signal_back_price"}, "Attribute", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Removed Columns1", "Prefix", each if [Value] is text then Text.BeforeDelimiter(Text.From([Value], "en-GB"), "-") else null, type text),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Header", each if [Value] is text then Text.AfterDelimiter(Text.From([Value], "en-GB"), "-") else null, type text),
#"Filled Down" = Table.FillDown(#"Inserted Text After Delimiter",{"Prefix", "Header"}),
#"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 0, 1),
#"Added Custom2" = Table.AddColumn(#"Added Index", "Keep", each Number.Mod([Index], 2) = 1),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Keep] = true) and ([Prefix] <> "signal_back_price")),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Keep"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Header]), "Header", "Value", List.Sum),
#"Removed Columns3" = Table.RemoveColumns(#"Pivoted Column",{"Count of name/value pairs in this Section before next Section column", "Automation instance"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns3",{"Time", "Market", "Section type:Event/Market/{Selection Name}", "Prefix", "signal_back_price", "back_price", "initial_stop_price", "layhigh", "laylow", "target_price", "trigger_high_traded_price", "trigger_low_traded_price", "trigger_no_of_selections", "trigger_sec_untill_start", "trigger_sel_vol", "trigger_sel_vol%"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Time", type datetime}, {"Market", type text}, {"Section type:Event/Market/{Selection Name}", type text}, {"Prefix", type text}, {"signal_back_price", type number}, {"back_price", type number}, {"initial_stop_price", type number}, {"layhigh", type number}, {"laylow", type number}, {"target_price", type number}, {"trigger_high_traded_price", type number}, {"trigger_low_traded_price", type number}, {"trigger_no_of_selections", type number}, {"trigger_sec_untill_start", type number}, {"trigger_sel_vol", type number}, {"trigger_sel_vol%", type number}})
in
#"Changed Type"
Thank you so much, that works a treat.
