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, on...
excel
office 365
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