Extract a value from a row

[Anonymous]

Hello, I want to extract the month and year that are added in my source above my "agent ID" column.

How to proceed from the query editor without impacting my agent ID column.

I would then like to create a Month column and a Year column that recovers my rows 4 and 5

thanks !!

[PhilipTreacy · Accepted solution]

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

[Anonymous]

Hello @PhilipTreacy 

For information it works well when I use only one source. However I work with a folder option. One source per month.
And when I add the other months, it does not work anymore.

It makes me a fill down of the last month and corrupts the whole lines. Do you have an idea to overcome this technical constraint? Thank you very much

[PhilipTreacy]

Hi @Anonymous 

Extracting the month and year is pretty simple but how do you want this data stored?  You want an entire column filled with Aout 2021?

Regards

Phil

[Anonymous]

thank you for the answer. Yes an entire column filled with the month and one with year please.