Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sipati
Employee
Employee

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

8 REPLIES 8
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.

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"

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.