Extract a value from a row

Hi Anonymous 

Download sample PBIX file with code/solution

I've started with a subset of your data but it works the same for your full dataset.  The code extracts the month and year and fills the entire column with what's extracted.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnVRCC3JzMksTizJzM/Tq8gpVtJRci1JLFFISVXISVQoTs1JTQZJKcXq4FAdkFqUmZtaUpSKW4mvv2dwvKOfo09ksKuVgmN+aQlutY5+fq6uCMVGBkaGeBS7u/qFxDu6ubk6hziGePr7KcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, #"Agent ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"Agent ID", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Month", each if Text.Contains([Agent ID], "MOIS_ANALYSE") then Text.Trim(Text.AfterDelimiter([Agent ID], ":")) else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Year", each if Text.Contains([Agent ID], "ANNEE_ANALYSE") then Text.Trim(Text.AfterDelimiter([Agent ID], ":")) else null),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Month", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Month"}),
    #"Sorted Rows1" = Table.Sort(#"Filled Down",{{"Year", Order.Descending}}),
    #"Filled Down1" = Table.FillDown(#"Sorted Rows1",{"Year"}),
    #"Sorted Rows2" = Table.Sort(#"Filled Down1",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows2",{"Index"})
in
    #"Removed Columns"

I don't have your file so you'll need to copy my code into yours from the #"Added Index" step onwards.

This is the result

Regards

Phil