Power Query to Split a Column

[andrewc]

Hi,

I have data in the attached workbook, arranged as a Table, and would like to use Power Query to split the data in column A into three columns with the person's name in one column, the date in the next and the region in the final column.

I have achieved this in Power Query Editor by splitting the column by delimeters (using " / " as the delimiter).

However, when I change the data type of the data in the date column from text to date, most of the dates are converted to "Error"

Can someone please tell me what I'm doing wrong?

Thanks!

[alansidman]

Here is the Mcode I used to get the data into three columns formatted correctly. I split on a space and not a "/". Then deleted the two columns holding the slash.

let
    Source = Excel.CurrentWorkbook(){[Name="StartSales"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Description", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Description.1", "Description.2", "Description.3", "Description.4", "Description.5"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Description.1", type text}, {"Description.2", type text}, {"Description.3", type date}, {"Description.4", type text}, {"Description.5", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Description.2", "Description.4"})
in
    #"Removed Columns"

[andrewc]

Hi,

Thanks for the response, Alan!

That yields the same results as I was getting, with "Error" for many of the dates.

Could the problem be because the "dates" in the original data are in a MM/DD format while my PC defaults to DD/MM?

[alansidman]

Interesting because I get no errors. Here is a short sample of what the results look like for me.

Data Range

	A	B	C
1	Description.1	Description.3	Description.5
2	Carlota	5/5/2016	West
3	Aspen	4/2/2016	South
4	Yanaki	5/2/2016	South
5	FlatTop	4/11/2016	South
6	Carlota	5/21/2016	South
7	Carlota	5/15/2016	East
8	Carlota	5/19/2016	West
9	Yanaki	5/6/2016	East
10	Yanaki	5/7/2016	East
11	Quad	5/20/2016	West
12	Quad	4/29/2016	East
13	Quad	4/18/2016	South
14	FlatTop	4/25/2016	West
15	Yanaki	5/30/2016	South
16	Carlota	5/4/2016	East
17	Carlota	5/18/2016	West
18	FlatTop	4/9/2016	West
19	Quad	5/6/2016	South
20	Quad	5/22/2016	East
21	FlatTop	4/28/2016	West
22	Aspen	5/16/2016	South
23	FlatTop	5/3/2016	East
24	Carlota	4/28/2016	East
25	Aspen	5/18/2016	West

Have checked to see what your regional settings are in PQ? They may be set differently than your Windows Regional settings.

[Bo_Ry]

Pleased try

let
    Source = Excel.CurrentWorkbook(){[Name="StartSales"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Description", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), {"Type", "Date", "Region"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Date", type date}}, "en-US")
in
    #"Changed Type with Locale"

[andrewc]

Thanks.

Changing the regional settings in PQ did the trick