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"
    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Dec 28, 2021

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

Resources