Log In
Register
Home
New Member
July 4, 2019
Offline
Hi
Im new to excel and am trying to build a production counting system to count product on a conveyor line. i am using a plc that data logs to cvs files which i then use power query to pickup every minute and bring into excel. I would like to build a dashboard to allow the operator to see there line performance.
I would like the charts to show the last 12 hours of production but have no idea how to achieve this, I have attached a basic copy of the spreadsheet if any you guys could help me out it would be much appreciated
Thank you
Craig
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Craig,
you should apply a filter to your query, to load only the last 12 hours data.
First, add a parameter to detect the highest date in your data, then filter data.
MaxDateTime= DateTime.From( List.Max( #"Previous Step name"[DateTimeColumn] ) ),
Use the power query editor interface and just filter the date column, uncheck one of the dates in the list just to make power query create the step for you, then edit the step to use the following criteria:
each [DateTimeField] >= MaxDateTime-#duration(0,12,0,0)
New Member
July 4, 2019
Offline
Hi Catalin
Thank you for your reply but im not good enough with excel to understand what to do could you possible add it to the file i have attached in my first post please
Thank you
Craig
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Try this :
Source = Folder.Files("C:\Users\c3ham\Documents\Count data"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.EndsWith([Extension], "csv")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Date accessed", "Date modified", "Date created", "Attributes", "Extension", "Name"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Count data", each #"Transform File from Count data"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Count data"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Count data", Table.ColumnNames(#"Transform File from Count data"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Time", type datetime}, {"VD0", Int64.Type}, {"VD4", Int64.Type}, {"VD8", Int64.Type}, {"VD12", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"VD0", "LD IN"}, {"VD4", "LD OUT/VIS IN"}, {"VD8", "VIS OUT"}, {"VD12", "VMS OUT"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Time", "Time - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Time", "Time - Copy", "LD IN", "LD OUT/VIS IN", "VIS OUT", "VMS OUT"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Time", "DATE"}, {"Time - Copy", "TIME"}}),
MaxDateTime= DateTime.From( List.Max( #"Changed Type"[Time] ) ),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns1", each ([LD IN] <> null and [Time] >= MaxDateTime-#duration(0,12,0,0) ) ),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"TIME", type time}}),
#"Extracted First Characters" = Table.TransformColumns(#"Changed Type1", {{"TIME", each Text.Start(Text.From(_, "en-GB"), 5), type text}}),
#"Filtered Rows2" = Table.SelectRows(#"Extracted First Characters", each Text.EndsWith([TIME], "00") or Text.EndsWith([TIME], "15") or Text.EndsWith([TIME], "30") or Text.EndsWith([TIME], "45")),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows2",{{"TIME", type time}, {"DATE", type date}})
in
#"Changed Type2"
1 Guest(s)
