Forum Discussion
undefined
Hi Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Click "transform data" to enter the power query editor, click "Advanced Editor", copy and paste the following code. Please check the steps in the Step column on the right.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY7BCoAgEET/Zc9BaUVdo44FQUfxsNQSUhiY/09qhEW3t+ybYYQABgkwlrKUZ5w7Lm/MHQ6oVzIOOtSKdpCJgJfisY7YKUOzPbw/ocFg+5bq1+2xHZuQQX2bRTTzL/aESxhjtmCW7uDx/do7zYq0Vad9JoOUFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"Open Date Application" = _t, #"Closed Date Application" = _t, Job = _t, Recruiter = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Application", Int64.Type}, {"Open Date Application", type date}, {"Closed Date Application", type date}, {"Job", type text}, {"Recruiter", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Open Date Application])..Number.From([Closed Date Application])}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}), #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Custom", type text}}, "en-US"), "Custom", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Custom.2] = null or [Custom.2] = 1)), #"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows", "Merged", each Text.Combine({Text.From([Custom.3], "en-US"), Text.From([Custom.1], "en-US"), Text.From([Custom.2], "en-US")}, "/"), type text), #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Custom.1", "Custom.2", "Custom.3"}), #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Merged", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Merged", "New Column to join Date table"}}) in #"Renamed Columns"(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Click "transform data" to enter the power query editor, click "Advanced Editor", copy and paste the following code. Please check the steps in the Step column on the right.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY7BCoAgEET/Zc9BaUVdo44FQUfxsNQSUhiY/09qhEW3t+ybYYQABgkwlrKUZ5w7Lm/MHQ6oVzIOOtSKdpCJgJfisY7YKUOzPbw/ocFg+5bq1+2xHZuQQX2bRTTzL/aESxhjtmCW7uDx/do7zYq0Vad9JoOUFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"Open Date Application" = _t, #"Closed Date Application" = _t, Job = _t, Recruiter = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Application", Int64.Type}, {"Open Date Application", type date}, {"Closed Date Application", type date}, {"Job", type text}, {"Recruiter", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Open Date Application])..Number.From([Closed Date Application])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Custom", type text}}, "en-US"), "Custom", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Custom.2] = null or [Custom.2] = 1)),
#"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows", "Merged", each Text.Combine({Text.From([Custom.3], "en-US"), Text.From([Custom.1], "en-US"), Text.From([Custom.2], "en-US")}, "/"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Merged", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Merged", "New Column to join Date table"}})
in
#"Renamed Columns"(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Hi, Thanks for sharing your answer! Actually it works when I use this code with this sample table but when I just want to copy the same process on other dataset that I have imported the table, It doesn't work. Maybe, it is happening because I am not familiar with M code. I am trying to figure it out how I can use your code in my side. Thank you
- Hi, Thanks for sharing your answer! Actually it works when I use this code with this sample table but when I just want to copy the same process on other dataset that I have imported the table, It doesn't work. Maybe, it is happening because I am not familiar with M code. I am trying to figure it out how I can use your code in my side. Thank you
Hi Anonymous ,
Please click the gear next to "Source" to modify the data source, taking care to keep the column names the same as the old data source.
Alternatively, you can follow these steps on the imported table:
1. Add a custom column.2. Expand the column.
3. Change the column type to date type.
4. Split the column into year, month and day, filter the day column to keep only the 1 and null columns, and then merge again.
All these functions can be found in the function bar on the top side of power query, you can click the gear to see the more detailed settings.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.