Forum Discussion
Duplicate rows by merging columns
I have a table like:
| ID | ReceivedDate | ResolvedDate |
| 1 | 12/01/2019 | 13/01/2019 |
| 2 | 14/01/2019 | 15/01/2019 |
I want the new table to be like:
| ID | Date |
| 1 | 12/01/2019 |
| 1 | 13/01/2019 |
| 2 | 14/01/2019 |
| 2 | 15/01/2019 |
So what will be the solution for this?
You can achieve using Power Query as below. Locale was used for correct date format, you can consider from Custom1 step.
Custom1 = Extracted dates from ReceivedDate and ResolvedDate columns and splitting them into multiple rows. It will work eveni if difference is more than 1 between dates.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00jcw1DcyMLQEcYzhnFidaCUjkJAJsrwpQj4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, ReceivedDate = _t, ResolvedDate = _t]), #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"ReceivedDate", type date}}, "en-GB"), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"ResolvedDate", type date}}, "en-GB"), Custom1 = Table.ExpandListColumn(Table.AddColumn(#"Changed Type","Date",each {Int64.From([ReceivedDate])..Int64.From([ResolvedDate])}),"Date"), #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Date", type date}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"ReceivedDate", "ResolvedDate"}) in #"Removed Columns"Thanks
Ankit JainDo Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.
8 Replies
You can achieve using Power Query as below. Locale was used for correct date format, you can consider from Custom1 step.
Custom1 = Extracted dates from ReceivedDate and ResolvedDate columns and splitting them into multiple rows. It will work eveni if difference is more than 1 between dates.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00jcw1DcyMLQEcYzhnFidaCUjkJAJsrwpQj4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, ReceivedDate = _t, ResolvedDate = _t]), #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"ReceivedDate", type date}}, "en-GB"), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"ResolvedDate", type date}}, "en-GB"), Custom1 = Table.ExpandListColumn(Table.AddColumn(#"Changed Type","Date",each {Int64.From([ReceivedDate])..Int64.From([ResolvedDate])}),"Date"), #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Date", type date}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"ReceivedDate", "ResolvedDate"}) in #"Removed Columns"Thanks
Ankit JainDo Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.
How do I extract rows from the existing table?
You've saved something in Source.
I want to make it like get QueryID, ResolvedDate, ReceivedDate columns from "NewQueryTable". There are other columns in the same table, as wellYou mainly need to add below two lines in your M Query. In Custom1, change "#"Changed Type" to your latest step in M. If still not clear, share your PBIX file.. Will change and Reshare.
Custom1 = Table.ExpandListColumn(Table.AddColumn(#"Changed Type","Date",each {Int64.From([ReceivedDate])..Int64.From([ResolvedDate])}),"Date"), #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Date", type date}}),
My solution
let
Source = Excel.CurrentWorkbook(){[Name="Tabla2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"ReceivedDate", type datetime}, {"ResolvedDate", type datetime}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Atributo", "Valor"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Atributo"})
in
#"Removed Columns"
