cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sipati
Microsoft
Microsoft

Duplicate rows by merging columns

I have a table like:

IDReceivedDateResolvedDate
112/01/201913/01/2019
214/01/201915/01/2019

 

I want the new table to be like:

IDDate
112/01/2019
113/01/2019
214/01/2019
215/01/2019

So what will be the solution for this?

1 ACCEPTED SOLUTION
AnkitBI
Solution Sage
Solution Sage

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.

View solution in original post

7 REPLIES 7
AnkitBI
Solution Sage
Solution Sage

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.

View solution in original post

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

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

 

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:

IDReceivedDateResolvedDate
112/01/2019 
214/01/201915/01/2019

 

Then output should be:

IDDate
112/01/2019
214/01/2019
215/01/2019

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.

Done.
Fixed it by doing:

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

Instead of:

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

 Thanks

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors