excel formula

You want to remove the suffixes -I, -II, -III

*formula  >>>>>   Text.BeforeDelimiter([Attribute], "-")

  date

= Table.TransformColumns(Unpivoted, {{"Dates", each Date.FromText(_, "en-GB"), type date}}) 

Breakdown:

• Source: the previous step name

• "Dates": the column you want to convert

• each Date.FromText(_, "en-GB"): convert text to date using UK format

• type date: explicitly set the column type

-----------------------------------------------------------------------------------

Total No. of Samples = count of numeric values per sample (excluding blanks or text)

                     Name       Total No. of Samples

formula  >>>>>     if [Value] is number then 1 else 0

 No. of Recoveries = count of values > 0 per sample

                             Name   No. of Recoveries

formula  >>>>>    if try [Value] > 0 then 1 else 0

                            or 

working formula             if (try [Value] > 0 otherwise false) then 1 else 0

Recovery %

    Formula >>>>  No. of Recoveries /  Total No. of Samples * 100

Week Start code

let

    // Step 1: Load your data (replace with your actual source)

    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],

    // Step 2: Change column types (if needed)

    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Sample Site Location", type text}}),

    // Step 3: Unpivot all date columns

    Unpivoted = Table.UnpivotOtherColumns(#"Changed Type", {"Sample Site Location"}, "Attribute", "Value"),

    // Step 4: Convert Attribute from text to date using UK format (dd/mm/yyyy)

    #"Converted Dates" = Table.TransformColumns(Unpivoted, {{"Attribute", each Date.FromText(_, "en-GB"), type date}}),

    // Step 5: Add Week Start column (week starts Monday)

    #"Added Week Start" = Table.AddColumn(#"Converted Dates", "Week Start", each Date.StartOfWeek([Attribute], Day.Monday))

in

    #"Added Week Start"

let

    // Step 1: Load source table

    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

    // Step 2: Change column types

    #"Changed Type" = Table.TransformColumnTypes(Source, {

        {"Sample Site Location", type text},

        {"02/04/25", Int64.Type}, {"06/04/25-I", Int64.Type}, {"06/04/25-II", Int64.Type},

        {"09/04/25-I", Int64.Type}, {"10/04/25", Int64.Type}, {"12/04/25-I", Int64.Type},

        {"12/04/25-II", Int64.Type}, {"12/04/25-III", Int64.Type}, {"18/04/25-I", Int64.Type},

        {"18/04/25-II", Int64.Type}, {"19/04/25", Int64.Type}, {"21/04/25", Int64.Type},

        {"22/04/25", Int64.Type}, {"25/04/25-I", Int64.Type}, {"25/04/25-II", Int64.Type},

        {"28/04/25-I", Int64.Type}, {"28/04/25-II", Int64.Type}

    }),

    // Step 3: Unpivot columns

    #"Unpivoted" = Table.UnpivotOtherColumns(#"Changed Type", {"Sample Site Location"}, "Attribute", "Value"),

    // Step 4: Remove -I, -II, -III suffixes

    #"Removed Suffix" = Table.AddColumn(#"Unpivoted", "DateText", each Text.BeforeDelimiter([Attribute], "-")),

    // Step 5: Convert to Date (UK format)

    #"Converted to Date" = Table.TransformColumns(#"Removed Suffix", {{"DateText", each Date.FromText(_, "en-GB"), type date}}),

    // Step 6: Rename new column to "Date"

    #"Renamed Column" = Table.RenameColumns(#"Converted to Date", {{"DateText", "Date"}}),

    // Step 7: Add Week Start column

    #"Added Week Start" = Table.AddColumn(#"Renamed Column", "Week Start", each Date.StartOfWeek([Date], Day.Monday))

in

    #"Added Week Start"

weeek 1 st week 2n week

.////////////////////////////

let

    // Step 1: Load data from Excel table

    Source = Excel.CurrentWorkbook(){[Name="Table2__2"]}[Content],

    // Step 2: Convert column types

    #"Changed Type" = Table.TransformColumnTypes(Source, {

        {"Sample Site Location", type text},

        {"Attribute", type text},

        {"Value", Int64.Type},

        {"Date", type datetime},

        {"Week Start", type datetime},

        {"Total No. of Samples", Int64.Type},

        {" No. of Recoveries", Int64.Type},

        {"Recovery %", Int64.Type}

    }),

    // Step 3: Remove time part from Week Start

    #"Date Only Week Start" = Table.TransformColumns(#"Changed Type", {{"Week Start", DateTime.Date}}),

    // Step 4: Get unique week start dates and sort them

    #"Sorted Weeks" = Table.Sort(

        Table.Distinct(Table.SelectColumns(#"Date Only Week Start", {"Week Start"})),

        {"Week Start", Order.Ascending}

    ),

    // Step 5: Add an index for week numbers starting from 1

    #"Added Index" = Table.AddIndexColumn(#"Sorted Weeks", "Week Number", 1, 1, Int64.Type),

    // Step 6: Join week number back to main table

    #"Merged Queries" = Table.NestedJoin(#"Date Only Week Start", {"Week Start"}, #"Added Index", {"Week Start"}, "WeekInfo", JoinKind.LeftOuter),

    #"Expanded WeekInfo" = Table.ExpandTableColumn(#"Merged Queries", "WeekInfo", {"Week Number"}),

    // Step 7: Add Week Label column with suffix logic

 #"Added Week Label" = Table.AddColumn(#"Expanded WeekInfo", "Week Label", each 

    Text.From([Week Number]) & 

    (

        if [Week Number] = 1 then "st Week" 

        else if [Week Number] = 2 then "nd Week" 

        else if [Week Number] = 3 then "rd Week" 

        else "th Week"

    ), 

    type text

)

in

    #"Added Week Label"