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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.