multiple dates

hershys

New Member
Joined
Apr 26, 2018
Messages
4
I'm trying to list multiple dates separated by comma. I understand that the following won't work: {“ShipDate”, each Text.Combine( [ShipDate] , “, “), type text} However, I tried to include Date.ToText without success. Anyone can help?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi, @hershys. Welcome to the MrExcel Message Board.

I am confused because you mentioned Text.Combine, so I am not sure what you have and what you are trying to get, and I will try two opposite solutions.

1- You basically have the following data in the worksheet:
Book1
A
1ShipDate
26/1/2021, 6/3/2021
35/4/2021, 5/6/2021
44/3/2021, 4/21/2021
Sheet1

And want to extract these two dates in Power Query?

Try following (Table1 is the name of the table)
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitColumn = Table.SplitColumn(Source, "ShipDate", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ShipDate.1", "ShipDate.2"}),
    Result = Table.TransformColumnTypes(SplitColumn,{{"ShipDate.1", type date}, {"ShipDate.2", type date}})
in
    Result
Result:
ShipDate.1ShipDate.2
6/1/20216/3/2021
5/4/20215/6/2021
4/3/20214/21/2021

2- Or, If I understood the opposite, and you actually have the following:
Book1
AB
1ShipDate.1ShipDate.2
26/1/20216/3/2021
35/4/20215/6/2021
44/3/20214/21/2021
Sheet2

And want the combine these two columns?
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"ShipDate.1", type date}, {"ShipDate.2", type date}}),
    Combined = Table.AddColumn(ChangeType, "Combined", each Text.Combine({Text.From([ShipDate.1]), Text.From([ShipDate.2])}, ","), type text)
in
    Combined
Result:
ShipDate.1ShipDate.2Combined
6/1/20216/3/20216/1/2021,6/3/2021
5/4/20215/6/20215/4/2021,5/6/2021
4/3/20214/21/20214/3/2021,4/21/2021

If either one is wrong, then perhaps you might try providing some sample data and desired result. You can use XL2BB to post sample mini-sheets easily.
 
Upvote 0
Hi, @hershys. Welcome to the MrExcel Message Board.

I am confused because you mentioned Text.Combine, so I am not sure what you have and what you are trying to get, and I will try two opposite solutions.

1- You basically have the following data in the worksheet:
Book1
A
1ShipDate
26/1/2021, 6/3/2021
35/4/2021, 5/6/2021
44/3/2021, 4/21/2021
Sheet1

And want to extract these two dates in Power Query?

Try following (Table1 is the name of the table)
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitColumn = Table.SplitColumn(Source, "ShipDate", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ShipDate.1", "ShipDate.2"}),
    Result = Table.TransformColumnTypes(SplitColumn,{{"ShipDate.1", type date}, {"ShipDate.2", type date}})
in
    Result
Result:
ShipDate.1ShipDate.2
6/1/20216/3/2021
5/4/20215/6/2021
4/3/20214/21/2021

2- Or, If I understood the opposite, and you actually have the following:
Book1
AB
1ShipDate.1ShipDate.2
26/1/20216/3/2021
35/4/20215/6/2021
44/3/20214/21/2021
Sheet2

And want the combine these two columns?
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"ShipDate.1", type date}, {"ShipDate.2", type date}}),
    Combined = Table.AddColumn(ChangeType, "Combined", each Text.Combine({Text.From([ShipDate.1]), Text.From([ShipDate.2])}, ","), type text)
in
    Combined
Result:
ShipDate.1ShipDate.2Combined
6/1/20216/3/20216/1/2021,6/3/2021
5/4/20215/6/20215/4/2021,5/6/2021
4/3/20214/21/20214/3/2021,4/21/2021

If either one is wrong, then perhaps you might try providing some sample data and desired result. You can use XL2BB to post sample mini-sheets easily.
Hi, @hershys. Welcome to the MrExcel Message Board.

I am confused because you mentioned Text.Combine, so I am not sure what you have and what you are trying to get, and I will try two opposite solutions.

1- You basically have the following data in the worksheet:
Book1
A
1ShipDate
26/1/2021, 6/3/2021
35/4/2021, 5/6/2021
44/3/2021, 4/21/2021
Sheet1

And want to extract these two dates in Power Query?

Try following (Table1 is the name of the table)
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitColumn = Table.SplitColumn(Source, "ShipDate", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ShipDate.1", "ShipDate.2"}),
    Result = Table.TransformColumnTypes(SplitColumn,{{"ShipDate.1", type date}, {"ShipDate.2", type date}})
in
    Result
Result:
ShipDate.1ShipDate.2
6/1/20216/3/2021
5/4/20215/6/2021
4/3/20214/21/2021

2- Or, If I understood the opposite, and you actually have the following:
Book1
AB
1ShipDate.1ShipDate.2
26/1/20216/3/2021
35/4/20215/6/2021
44/3/20214/21/2021
Sheet2

And want the combine these two columns?
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"ShipDate.1", type date}, {"ShipDate.2", type date}}),
    Combined = Table.AddColumn(ChangeType, "Combined", each Text.Combine({Text.From([ShipDate.1]), Text.From([ShipDate.2])}, ","), type text)
in
    Combined
Result:
ShipDate.1ShipDate.2Combined
6/1/20216/3/20216/1/2021,6/3/2021
5/4/20215/6/20215/4/2021,5/6/2021
4/3/20214/21/20214/3/2021,4/21/2021

If either one is wrong, then perhaps you might try providing some sample data and desired result. You can use XL2BB to post sample mini-sheets easily.
Wow! Thanks for your effort to help me!
Your second example is closer to what I'm doing and I tried using Text.From but results in error. I can only modify M recorded steps. So I'm doing a "group by" step (on order number) and Sum aggregation on Ship Dates, then modifying it from each List.Sum to each Text.Combine. I've done this on other text fields successfully but I need to get the dates as text.
 
Upvote 0
Wow! Thanks for your effort to help me!
Your second example is closer to what I'm doing and I tried using Text.From but results in error. I can only modify M recorded steps. So I'm doing a "group by" step (on order number) and Sum aggregation on Ship Dates, then modifying it from each List.Sum to each Text.Combine. I've done this on other text fields successfully but I need to get the dates as text.
Here's the step as modified:
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Channel Order #"}, {{"Total", each List.Sum([CustomTotal]), type nullable number}, {"Payments", each List.Sum([Payments]), type number}, {"Refunds", each List.Sum([Payments]), type number}, {"Order Dates", each Text.Combine(Text.From([Order Date]),","), type text}, {"Ship Dates", each Text.Combine(Text.From([Ship Date]),","), type text}, {"SC Orders", each Text.Combine([#"Order #"],","), type text}})
in
 
Upvote 0
That would be really great if you could post sample data (XL2BB is the best way) and use BB code when you post sample code. Then the helpers can work with your data easily and quickly provide solutions.

There might be other solutions, but here is one approach.

This is the data:
Book1
ABCDEF
1Channel Order #Order #PaymentsCustomTotalOrderDateShipDate
211_11010005/29/20216/1/2021
311_22020004/3/20215/4/2021
422_13030003/2/20214/3/2021
Source

The following is the M Code (I used the little M button on the editor toolbar, so it is more readable now):
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    ChangeToDate = Table.TransformColumnTypes(Source, {{"OrderDate", type date}, {"ShipDate", type date}}),
    ChangeToText = Table.TransformColumnTypes(ChangeToDate,{{"OrderDate", type text}, {"ShipDate", type text}}),
    GroupRows = Table.Group(ChangeToText,
        {"Channel Order #"},
        {
            {"Total", each List.Sum([CustomTotal]), type nullable number},
            {"Payments", each List.Sum([Payments]), type nullable number},
            {"Refunds", each List.Sum([Payments]), type nullable number},
            {"SC Orders", each Text.Combine([#"Order #"],","), type text},
            {"Order Dates", each Text.Combine([OrderDate] , ", "), type text},
            {"Ship Dates", each Text.Combine([ShipDate] , ", "), type text}
        })
in
    GroupRows
And the following is the result:
Channel Order #TotalPaymentsRefundsSC OrdersOrder DatesShip Dates
1300030301_1,1_25/29/2021, 4/3/20216/1/2021, 5/4/2021
2300030302_13/2/20214/3/2021

The idea is, changing date field types to date initially, then text (I did that to avoid the hour part - there might be other methods). Then you can use the date fields as text with the Text.Combine function.

Hope this helps.
 
Upvote 0
Solution
That would be really great if you could post sample data (XL2BB is the best way) and use BB code when you post sample code. Then the helpers can work with your data easily and quickly provide solutions.

There might be other solutions, but here is one approach.

This is the data:
Book1
ABCDEF
1Channel Order #Order #PaymentsCustomTotalOrderDateShipDate
211_11010005/29/20216/1/2021
311_22020004/3/20215/4/2021
422_13030003/2/20214/3/2021
Source

The following is the M Code (I used the little M button on the editor toolbar, so it is more readable now):
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    ChangeToDate = Table.TransformColumnTypes(Source, {{"OrderDate", type date}, {"ShipDate", type date}}),
    ChangeToText = Table.TransformColumnTypes(ChangeToDate,{{"OrderDate", type text}, {"ShipDate", type text}}),
    GroupRows = Table.Group(ChangeToText,
        {"Channel Order #"},
        {
            {"Total", each List.Sum([CustomTotal]), type nullable number},
            {"Payments", each List.Sum([Payments]), type nullable number},
            {"Refunds", each List.Sum([Payments]), type nullable number},
            {"SC Orders", each Text.Combine([#"Order #"],","), type text},
            {"Order Dates", each Text.Combine([OrderDate] , ", "), type text},
            {"Ship Dates", each Text.Combine([ShipDate] , ", "), type text}
        })
in
    GroupRows
And the following is the result:
Channel Order #TotalPaymentsRefundsSC OrdersOrder DatesShip Dates
1300030301_1,1_25/29/2021, 4/3/20216/1/2021, 5/4/2021
2300030302_13/2/20214/3/2021

The idea is, changing date field types to date initially, then text (I did that to avoid the hour part - there might be other methods). Then you can use the date fields as text with the Text.Combine function.

Hope this helps.
Yes! This did it! Thanks
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top