Good morning! I was wondering if anyone could help me solve a problem that just arose at work.
We have a machine at our factory that gives data on it's production stages throughout each day and the file was given to me to see what i could do with it.
I noticed right away that because there was some information missing in a one of the first columns for a bunch of lines, the file that was generated and given to me ended up being distorted in the sense that the lines that don't have the missing information moved all of the information from the next columns into the previous one, confusing, I know.
So I have columns that should only have stage names that end up having number values as well. Better seen then explained, so I'm attaching a file example and a picture. How do i move all of the information from those lines one column over while maintaining the lines that have the complete information as is?
Thank you in advance!
Regards
Eduardo
I'm sure there is a fancier way, but you could use a brute-force approach:
-add one calculated column for each value column that check if the last column in the original dataset is null, then take the previous column's value, otherwise use the current column.
Edit:
There may be a simpler way (like fixing whatever produces this report!), but this seems to work with your sample file:
let
Source = Csv.Document(File.Contents("C:path to file20210701Paster_HRS.csv"),[Delimiter=",", Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None]),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
OriginalColumnNames = Table.ColumnNames(PromotedHeaders),
MakeAList = Table.AddColumn(PromotedHeaders, "Custom", each Record.ToList(_)),
ReorderedList = Table.AddColumn(MakeAList, "NewList", each if List.Last([Custom]) ="" then List.FirstN(List.InsertRange([Custom], 2, {""}), List.Count(OriginalColumnNames)) else [Custom]),
AddRecord = Table.AddColumn(ReorderedList, "NewRecord", each Record.FromList([NewList], OriginalColumnNames)),
#"Removed Columns" = Table.RemoveColumns(AddRecord,{"Date", "Time", "Fase", "FT301[L/H]", "TT101[°C]", "TT303[°C]", "TT304[°C]", "TT306[°C]", "FCV301[%]", "FCV302[%]", "AV302_AUTO", "AV302_MANUAL", "AV305", "Custom", "NewList"}),
#"Expanded NewRecord" = Table.ExpandRecordColumn(#"Removed Columns", "NewRecord", {"Date", "Time", "Fase", "FT301[L/H]", "TT101[°C]", "TT303[°C]", "TT304[°C]", "TT306[°C]", "FCV301[%]", "FCV302[%]", "AV302_AUTO", "AV302_MANUAL", "AV305"}, {"Date", "Time", "Fase", "FT301[L/H]", "TT101[°C]", "TT303[°C]", "TT304[°C]", "TT306[°C]", "FCV301[%]", "FCV302[%]", "AV302_AUTO", "AV302_MANUAL", "AV305"})
in
#"Expanded NewRecord"
