Dec 27 2021 08:03 PM
Thank you in advance for taking your time to help me.
I made a workbook to organize the staff roster, I create the roster in the DETAILED ROSTER sheet where I have set all my boundaries and info, once the roster it is filled up I populate a printable version by getting all the necessary info from the DATAILED ROSTER sheet. I used a query and connection from table/range and after I trim all the unnecessary I close and load.
Now the problem
It seams to works fine until it populate Saturday where it looks like it is loosing the format and give me the wrong information, I have marked in red where the problem occurred. I have checked the cells format in all the sheets involved in the process and they are all fine (unless I missing the obvious).
I can`t really understand why it does it, am I missing something or excel doesn`t like me lol
I have attached the file
Thank you for your time
Dec 28 2021 12:08 AM
Solution@olopa67 The data types were set incorrectly. Better to do that at the end when you only have the relevant columns left and set them as "Time". See attached.
Dec 28 2021 01:21 AM
Dec 28 2021 01:36 AM
@olopa67 Data typing in PQ is what's most important here.
Dec 28 2021 02:21 AM
Bit of the coding on the top not to hardcode column names
let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
allColumns = Table.ColumnNames (Source),
сolumnsToKeep = List.Select(
allColumns,
each
_ = "NAME" or
Text.EndsWith(_, "START") or
Text.EndsWith(_, "FINISH") ),
#"Removed Other Columns" = Table.SelectColumns(
Source,
сolumnsToKeep),
#"Filtered Rows" = Table.SelectRows(
#"Removed Other Columns",
each ([NAME] <> "DAY ROSTERED")),
#"Replaced Value" = Table.ReplaceValue(
#"Filtered Rows",
"weekly hours",
"",
Replacer.ReplaceText,{"NAME"}),
#"Changed Type" = Table.TransformColumnTypes(
#"Replaced Value",
List.Transform(
List.Skip( сolumnsToKeep ),
each {_, type time}
)
)
in
#"Changed Type"
Dec 28 2021 12:08 AM
Solution@olopa67 The data types were set incorrectly. Better to do that at the end when you only have the relevant columns left and set them as "Time". See attached.