Forum Discussion
Split column by second space
In M Query, my dataset contains a column holding file names. The file name can have any number of words in it, but the last words are always the month and year. The month however can be truncate...
Hi Anonymous ,
Try this:
1. Duplicate column.
2. Extracted Text Before Delimiter.
3. Extracted Text After Delimiter.
Then, change column names.
Attach the complete code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMSTVUMDIwtFTwSsxTitWBCBlBhNxSk+BCxhAh38Si5Ay4oIlCcXExRMIlNTk1Nym1SCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"), #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Column1 - Copy", "Column1 - Copy - Copy"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Duplicated Column1", {{"Column1 - Copy", each Text.BeforeDelimiter(_, " ", {1, RelativePosition.FromEnd}), type text}}), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Column1 - Copy - Copy", each Text.AfterDelimiter(_, " ", {1, RelativePosition.FromEnd}), type text}}), #"Renamed Columns" = Table.RenameColumns(#"Extracted Text After Delimiter",{{"Column1 - Copy", "File"}, {"Column1 - Copy - Copy", "Year Month"}}) in #"Renamed Columns"Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Try this:
1. Duplicate column.
2. Extracted Text Before Delimiter.
3. Extracted Text After Delimiter.
Then, change column names.
Attach the complete code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMSTVUMDIwtFTwSsxTitWBCBlBhNxSk+BCxhAh38Si5Ay4oIlCcXExRMIlNTk1Nym1SCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Column1 - Copy", "Column1 - Copy - Copy"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Duplicated Column1", {{"Column1 - Copy", each Text.BeforeDelimiter(_, " ", {1, RelativePosition.FromEnd}), type text}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Column1 - Copy - Copy", each Text.AfterDelimiter(_, " ", {1, RelativePosition.FromEnd}), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text After Delimiter",{{"Column1 - Copy", "File"}, {"Column1 - Copy - Copy", "Year Month"}})
in
#"Renamed Columns"
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.