SOLVED

data from table/range stop working half way and give me wrong result

Occasional Contributor

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

4 Replies
best response confirmed by olopa67 (Occasional Contributor)
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.

you are a legend thank you so much for your help.
just for my better understanding do you mean the data in the main roster or the data in the query were set wrongly?
again thank you for taking your time

@olopa67 Data typing in PQ is what's most important here.

@olopa67 

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"