Forum Discussion

olopa67's avatar
olopa67
Brass Contributor
Dec 28, 2021
Solved

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

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

excel
office 365

4 Replies

    • olopa67's avatar
      olopa67
      Brass Contributor
      Dec 28, 2021
      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
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Dec 28, 2021

        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"

Resources