PowerQuery transformation

Hmmmh  ... thanks or accepting my solution but I didn't fix it. 
I meerly spotted the other solutions from burakkaragoz  and Elena_Kalina may not work.

Hi powerbricco ,

You can refer to steps as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZY7bsMwDIbv4jloSIqU5LEHaIemnYoMaZGxDwTo/SvSkczFmzQYBmhRovyT5gfZ7+8TTofp9PZUbMBYLM86kwGKTWxWsq5SUsuiM5E1JmmkiMZInnWvhGJRNEZAYzjKdD4sKr/Xy63cKOsGWmJsmC1S1CJTsZYCc7IH6JhmSy3p47MNNYxgWaMm8XH5LpfmaUFRbTARlmVsIlksA7aiTQWhzaC9hhCTjYPtsrKKuuqcfv5un9eH58vXtcw+vrzqWjwCHgmsKhTvsHeCd8g76B1wDszeyd5J3vEZgM8AfAbgM4A1Ay2MGgkRZQgPVeVOAiN356FKNBIWSLrzsEmCb8oesdDCQiNBrOP9eagqdxKCBfXloUo0EnCGETxskuCbskcstDBuJLC9vf48VJX6dVj60JeHqrEeCoFHALGJgu/KHrnQwqShkAmGAFFV6uchpAFAVJH1VyGHEURssUC+LXsEQwuL67FAPISIqnJnAY2F3kRUkfVcyGkEEefzPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, ART = _t, #"15/01/2025" = _t, #"14/01/2025" = _t, #"13/01/2025" = _t, #"12/01/2025" = _t, #"11/01/2025" = _t, #"10/01/2025" = _t, #"09/01/2025" = _t, #"08/01/2025" = _t, #"07/01/2025" = _t, #"06/01/2025" = _t, #"05/01/2025" = _t, #"04/01/2025" = _t, #"03/01/2025" = _t, #"02/01/2025" = _t, #"01/01/2025" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}}),
    #"Added Conditional Column1" = Table.AddColumn(#"Changed Type", "Source.Name", each if [Column1] = "Source.Name" then null else [Column1]),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column1",{"Source.Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column1"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Column2","Source.Name"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom1", each if Text.Contains([Value], "/") then [Value] else null),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom", each let _SN = [Source.Name], _Attribute = [Attribute] in Table.SelectRows(#"Added Conditional Column",each [Source.Name] = _SN and [Attribute] = _Attribute and [Custom1] = null)[Value]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom1] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Column2", "Source.Name", "Attribute", "Value"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "ART", each "SUM"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"ART", "Custom1", "Custom"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Custom1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom1.1", "Custom1.2", "Custom1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom1.1", Int64.Type}, {"Custom1.2", Int64.Type}, {"Custom1.3", Int64.Type}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"ART", "Custom1.2", "Custom1.1", "Custom1.3", "Custom"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns1", {{"Custom1.2", type text}, {"Custom1.1", type text}, {"Custom1.3", type text}}, "zh-CN"),{"Custom1.2", "Custom1.1", "Custom1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Custom", "Value"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

Original:

Result is as below.

Best Regards.

Rico Zhou

Hi powerbricco 

Sorry, please, I did not immediately pay attention to your specific table, since in each subsequent line with the dates there was a shift for one day, while in your final version you would like to see only the first mention.

In accordance with this, I edited all the steps that you must perform:

// Step 1: Use first row as headers
= Table.PromoteHeaders(#"PreviousStep", [PromoteAllScalars=true])

// Step 2: Remove Source.Name column
= Table.RemoveColumns(#"Promoted Headers",{"Source.Name"})

// Step 3: Filter out banana and pear from ART column
= Table.SelectRows(#"Removed Columns", each ([ART] <> "banana" and [ART] <> "pear"))

// Step 4: Unpivot other columns (keeping ART as identifier)
= Table.UnpivotOtherColumns(#"Filtered Rows", {"ART"}, "Attribute", "Value")

// Step 5: Add index column starting from 1
= Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type)

// Step 6: Filter to keep only needed rows (≤15 and specific indexes)
= Table.SelectRows(
    #"Added Index",
    each Number.From([Index]) <= 15 or 
    List.Contains({31, 61, 91, 121, 151}, Number.From([Index]))
)

// Step 7: Replace dates for specific indexes
= Table.ReplaceValue(
    #"Filtered Index Rows",
    each [Date],
    each if [Index] = 31 then #date(2025, 1, 16)
         else if [Index] = 61 then #date(2025, 1, 17)
         else if [Index] = 91 then #date(2025, 1, 18)
         else if [Index] = 121 then #date(2025, 1, 19)
         else if [Index] = 151 then #date(2025, 1, 20)
         else [Date],
    Replacer.ReplaceValue,
    {"Date"}
)

// Step 8: Rename 'Attitude' column to 'Date'
= Table.RenameColumns(#"PreviousStep", {{"Attitude", "Date"}})

// Step 9: Convert text dates to proper date format
= Table.TransformColumns(
    #"Renamed Columns", 
    {
        {"Date", each 
            try Date.FromText(_, "en-GB")    // Try day/month/year format first
            otherwise 
            try Date.FromText(_, "en-US")    // Then try month/day/year
            otherwise null,                   // Return null if conversion fails
            type date}
    }
)

// Step 10: Correct specific dates based on Index
= Table.ReplaceValue(
    #"Converted Date Format",
    each [Date],
    each if [Index] = 31 then #date(2025, 1, 16)
         else if [Index] = 61 then #date(2025, 1, 17)
         else if [Index] = 91 then #date(2025, 1, 18)
         else if [Index] = 121 then #date(2025, 1, 19)
         else if [Index] = 151 then #date(2025, 1, 20)
         else [Date],
    Replacer.ReplaceValue,
    {"Date"}
)

// Step 11: Sort by Date column
= Table.Sort(#"Corrected Specific Dates", {{"Date", Order.Ascending}})

// Step 12: Remove Index column
= Table.RemoveColumns(#"Sorted Rows", {"Index"})

Here is a screenshot with my result if you suddenly do not understand something, write to explain