Duplicate rows by merging columns

[sipati]

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?

[AnkitBI · Accepted solution]

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 Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

[ManuMMI]

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"

[sipati]

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 well

[AnkitBI]

You 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}}),

[sipati]

I am facing an issue, the IDs which don't have ResolvedDate in the original table, even they are weirdly getting dates. So how to handle this null/blank case? 

Also, for some reason all the inserted dates in this new table are wrong.

I want it to be like if:

ID	ReceivedDate	ResolvedDate
1	12/01/2019
2	14/01/2019	15/01/2019

Then output should be:

ID	Date
1	12/01/2019
2	14/01/2019
2	15/01/2019

[AnkitBI]

Try Below

Custom1 = Table.ExpandListColumn(Table.AddColumn(#"Changed Type","Date",each if [ResolvedDate] = null then {Int64.From([ReceivedDate])} else {Int64.From([ReceivedDate]),Int64.From([ResolvedDate])}),"Date"),

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

[sipati]

Done.
Fixed it by doing:

 Int64.From([ReceivedDate]), Int64.From([ResolvedDate]) 

Instead of:

Int64.From([ReceivedDate])..Int64.From([ResolvedDate])

 Thanks

[sipati]

Ok, I was able to do using:

let
    Source = NewQueryTable,
    Custom = Table.ExpandListColumn(Table.AddColumn(Source,"Date",each {Int64.From([ReceivedDate])..Int64.From([ResolvedDate])}),"Date"),
    #"Changed Type" = Table.TransformColumnTypes(Custom,{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ReceivedDate", "ResolvedDate"})
in
    #"Removed Columns"

But it is generating 300+ rows for like every ID, I don't want every date between a received-resolved date range. I want only received, resolved date. I think 300+ rows are there because it is considering range.