Excel Forum

Power Query: Column Data

Eddies Lima

Active Member

Members

Forum Posts: 4

Member Since:
February 12, 2021

Offline

January 12, 2022 - 10:20 pm

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

Attachments

Power-Query.png (232 KB)

See Answer

Velouria

London or thereabouts

Moderator

Members

Trusted Members

Moderators

Forum Posts: 689

Member Since:
November 1, 2018

Offline

January 13, 2022 - 1:50 am

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 file\20210701Paster_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"