Power query question - dates into columns

Mendy32 · Jul 23, 2020

Hi,

I need help.!! how can I combine the names to one row but have all the dates in columns with the Level underneath the Dates...Can this be done power query?

I need to look like this for example

I.d	Name	DOB	RAE	SEX	ADMIT	3.9.2020	3.16.2020	3.25.2020	etc...
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	H0	H0	H0
7848	Superman	8/31/1976	White	Male	6/4/2020	H0	H0	H0	H0

The list below is what I need combined...

I.d #	Name	DOB	RAE	SEX	ADMIT	Level	Dates
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	3.9.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	3.16.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	3.25.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	4.1.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	4.8.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	4.15.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	4.22.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	4.29.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	5.6.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	5.20.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	5.27.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	6.3.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	6.10.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	6.17.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	6.24.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	7.1.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	7.8.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	5.13.2020
7848	Superman	8/31/1976	White	Male	3/6/2020	H0	3.16.2020
7848	Superman	8/31/1976	White	Male	6/4/2020	H0	6.10.2020
7848	Superman	8/31/1976	White	Male	6/4/2020	H0	6.17.2020
7848	Superman	8/31/1976	White	Male	6/4/2020	H0	6.24.2020
7848	Superman	8/31/1976	White	Male	6/4/2020	H0	7.1.2020
7848	Superman	8/31/1976	White	Male	6/4/2020	H0	7.8.2020
7848	Superman	8/31/1976	White	Male	6/4/2020	H0	7.15.2020
7848	Superman	8/31/1976	White	Male	6/4/2020	H0	7.22.2020
1587	ROQUE	11/8/1993	PINK	Female	2/25/2020	H0	3.9.2020
6584	BATMAN	3/20/1992	ORANGE	Male	10/4/2019	H0	5.20.2020
6584	BATMAN	3/20/1992	ORANGE	Male	10/4/2019	H0	5.27.2020
6584	BATMAN	3/20/1992	ORANGE	Male	10/4/2019	H0	6.3.2020
6584	BATMAN	3/20/1992	ORANGE	Male	10/4/2019	H0	6.10.2020
6584	BATMAN	3/20/1992	ORANGE	Male	10/4/2019	H0	6.17.2020
6584	BATMAN	3/20/1992	ORANGE	Male	10/4/2019	H0	6.24.2020
6584	BATMAN	3/20/1992	ORANGE	Male	10/4/2019	H0	7.1.2020
6584	BATMAN	3/20/1992	ORANGE	Male	10/4/2019	H0	7.8.2020
6584	BATMAN	3/20/1992	ORANGE	Male	10/4/2019	H0	7.15.2020
6584	BATMAN	3/20/1992	ORANGE	Male	10/4/2019	H0	7.22.2020
6584	BATMAN	3/20/1992	ORANGE	Male	10/4/2019	H0	5.13.2020
749	Aquaman	8/14/1994	Brown	Male	3/2/2020	H0	3.9.2020
749	Aquaman	8/14/1994	Brown	Male	3/2/2020	H0	3.16.2020
749	Aquaman	8/14/1994	Brown	Male	3/2/2020	H0	3.25.2020
749	Aquaman	8/14/1994	Brown	Male	3/2/2020	H0	4.1.2020
749	Aquaman	8/14/1994	Brown	Male	3/2/2020	H0	4.8.2020
749	Aquaman	8/14/1994	Brown	Male	3/2/2020	H0	4.15.2020
83870	FLASH	6/24/1992	Brown	Male	2/24/2020	H0	3.9.2020
83870	FLASH	6/24/1992	Brown	Male	2/24/2020	H0	3.16.2020
83870	FLASH	6/24/1992	Brown	Male	2/24/2020	H0	7.8.2020
83870	FLASH	6/24/1992	Brown	Male	2/24/2020	H0	7.15.2020
83870	FLASH	6/24/1992	Brown	Male	2/24/2020	H0	7.22.2020
955882	THOR	9/13/1985	Brown	Male	6/17/2020	H0	6.24.2020
787667	IRONMAN	1/2/1972	White	Male	10/31/2019	H0	5.20.2020
787667	IRONMAN	1/2/1972	White	Male	10/31/2019	H0	5.27.2020
787667	IRONMAN	1/2/1972	White	Male	10/31/2019	H0	6.3.2020
787667	IRONMAN	1/2/1972	White	Male	10/31/2019	H0	6.10.2020
787667	IRONMAN	1/2/1972	White	Male	10/31/2019	H0	6.17.2020
787667	IRONMAN	1/2/1972	White	Male	10/31/2019	H0	6.24.2020
787667	IRONMAN	1/2/1972	White	Male	10/31/2019	H0	7.1.2020
787667	IRONMAN	1/2/1972	White	Male	10/31/2019	H0	7.8.2020
787667	IRONMAN	1/2/1972	White	Male	10/31/2019	H0	7.15.2020
787667	IRONMAN	1/2/1972	White	Male	10/31/2019	H0	7.22.2020
787667	IRONMAN	1/2/1972	White	Male	10/31/2019	H0	5.13.2020
5917854	PRINCE	4/4/1988	White	Male	4/19/2020	H1	4.22.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	4.15.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	4.22.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	4.29.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	5.6.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	5.20.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	5.27.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	6.3.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	6.10.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	6.17.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	6.24.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	7.1.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	7.8.2020
57421	MICKEY MOUSE	2/5/1970	PINK	Female	9/10/2019	H1	5.13.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	3.16.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	3.25.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	4.1.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	4.8.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	4.15.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	4.22.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	4.29.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	5.6.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	5.20.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	5.27.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	6.3.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	6.10.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	6.17.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	6.24.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	7.1.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	7.8.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	7.15.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	7.22.2020
5896907	LASSO	7/9/1993	ORANGE	Male	3/9/2020	H0	5.13.2020
719	CROWN	3/23/1993	ORANGE	Male	7/17/2020	H1	7.22.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	3.25.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	4.1.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	4.8.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	4.15.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	4.22.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	4.29.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	5.6.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	5.20.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	5.27.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	6.3.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	6.10.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	6.17.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	6.24.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	7.1.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	7.8.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	7.15.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	7.22.2020
5731998	RED BOOTS	9/13/1994	PINK	Male	8/30/2019	H1	5.13.2020

C Moore · Jul 23, 2020

This works with exact data supplied. Hopefully can get you the rest of the way.
Paste in Advanced Editor, then fix Source in line 1 and "--Start Dates--"
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"I.d #", type text}, {"Name", type text}, {"DOB", type text}, {"RAE", type text}, {"SEX", type text}, {"ADMIT", type text}, {"Level", type text}, {"Dates", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"I.d #", "Name", "DOB", "RAE", "SEX", "ADMIT"}),
#"--End Unique IDs--" = Table.Distinct(#"Removed Other Columns"),
#"--Start Dates--" = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Other Columns1" = Table.SelectColumns(#"--Start Dates--",{"I.d #", "Level", "Dates"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"Dates", type date}}),
#"--End Dates to Columns--" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type1", {{"Dates", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type1", {{"Dates", type text}}, "en-US")[Dates]), "Dates", "Level"),
#"Merged Queries" = Table.NestedJoin(#"--End Unique IDs--", {"I.d #"}, #"--End Dates to Columns--", {"I.d #"}, "--End Dates to Columns--", JoinKind.LeftOuter),
#"Expanded --End Dates to Columns--" = Table.ExpandTableColumn(#"Merged Queries", "--End Dates to Columns--", {"I.d #", "3/9/2020", "3/16/2020", "3/25/2020", "4/1/2020", "4/8/2020", "4/15/2020", "4/22/2020", "4/29/2020", "5/6/2020", "5/20/2020", "5/27/2020", "6/3/2020", "6/10/2020", "6/17/2020", "6/24/2020", "7/1/2020", "7/8/2020", "5/13/2020", "7/15/2020", "7/22/2020"}, {"I.d #.1", "3/9/2020", "3/16/2020", "3/25/2020", "4/1/2020", "4/8/2020", "4/15/2020", "4/22/2020", "4/29/2020", "5/6/2020", "5/20/2020", "5/27/2020", "6/3/2020", "6/10/2020", "6/17/2020", "6/24/2020", "7/1/2020", "7/8/2020", "5/13/2020", "7/15/2020", "7/22/2020"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded --End Dates to Columns--",{"I.d #.1"})
in
#"Removed Columns"

Mendy32 · Jul 23, 2020

I'm trying and I keep giving me an error...can you send it and example on how it will look

thanks

sandy666 · Jul 23, 2020

Mendy32 said:
I'm trying and I keep giving me an error...can you send it and example on how it will look

thanks

in Power Query Editor from Applied Steps remove all steps like: #"Changed Type" (luckily only two

)

sandy666 · Jul 23, 2020

@C Moore
try to use code tags [CODE]code here[/CODE]

Mendy32 · Jul 23, 2020

I'm still having issues...I get Syntax Error: invalid identifier

sandy666 · Jul 24, 2020

I changed whole M and got this



I.d #	Name	DOB	RAE	SEX	ADMIT	09/03/2020	16/03/2020	25/03/2020	01/04/2020	08/04/2020	15/04/2020	22/04/2020	29/04/2020	06/05/2020	20/05/2020	27/05/2020	03/06/2020	10/06/2020	17/06/2020	24/06/2020	01/07/2020	08/07/2020	13/05/2020	15/07/2020	22/07/2020
719	CROWN	23/03/1993	ORANGE	Male	17/07/2020																				H1
749	Aquaman	14/08/1994	Brown	Male	03/02/2020	H0	H0	H0	H0	H0	H0
1587	ROQUE	11/08/1993	PINK	Female	25/02/2020	H0
6584	BATMAN	20/03/1992	ORANGE	Male	10/04/2019										H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0
6858	Wonder Woman	15/09/2000	PINK	Male	16/02/2020	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0
7848	Superman	31/08/1976	White	Male	06/04/2020													H0	H0	H0	H0	H0		H0	H0
7848	Superman	31/08/1976	White	Male	03/06/2020		H0
57421	MICKEY MOUSE	02/05/1970	PINK	Female	09/10/2019						H1	H1	H1	H1	H1	H1	H1	H1	H1	H1	H1	H1	H1
83870	FLASH	24/06/1992	Brown	Male	24/02/2020	H0	H0															H0		H0	H0
787667	IRONMAN	01/02/1972	White	Male	31/10/2019										H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0
955882	THOR	13/09/1985	Brown	Male	17/06/2020															H0
5731998	RED BOOTS	13/09/1994	PINK	Male	30/08/2019			H1	H1	H1	H1	H1	H1	H1	H1	H1	H1	H1	H1	H1	H1	H1	H1	H1	H1
5896907	LASSO	07/09/1993	ORANGE	Male	03/09/2020		H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0

Rich (BB code):

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Distinct = Table.Distinct(Source),
    TypeDOB = Table.TransformColumnTypes(Distinct, {{"DOB", type date}}, "en-US"),
    TypeAdmit = Table.TransformColumnTypes(TypeDOB, {{"ADMIT", type date}}, "en-US"),
    TypeDates = Table.TransformColumnTypes(TypeAdmit, {{"Dates", type date}}, "en-US"),
    Pivot = Table.Pivot(Table.TransformColumnTypes(TypeDates, {{"Dates", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(TypeDates, {{"Dates", type text}}, "en-GB")[Dates]), "Dates", "Level")
in
    Pivot

sandy666 · Jul 24, 2020

Mendy32 said:
I'm still having issues...I get Syntax Error: invalid identifier

probably you removed it manually from Advanced Editor
you should just click on X in front of the step eg.

sandy666 · Jul 24, 2020

or even shorter

Rich (BB code):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeDOB = Table.TransformColumnTypes(Source, {{"DOB", type date}}, "en-US"),
    TypeAdmit = Table.TransformColumnTypes(TypeDOB, {{"ADMIT", type date}}, "en-US"),
    TypeDates = Table.TransformColumnTypes(TypeAdmit, {{"Dates", type date}}, "en-US"),
    Pivot = Table.Pivot(Table.TransformColumnTypes(TypeDates, {{"Dates", type text}}), List.Distinct(Table.TransformColumnTypes(TypeDates, {{"Dates", type text}})[Dates]), "Dates", "Level")
in
    Pivot

the result is the same as above

Mendy32 · Jul 24, 2020

Thank you it worked!

Power query question - dates into columns

Mendy32

New Member

Excel Facts

C Moore

Well-known Member

Mendy32

New Member

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

Mendy32

New Member

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

Mendy32

New Member

Similar threads

Forum statistics

Share this page

Power query question - dates into columns

New Member

Excel Facts

Well-known Member

New Member

Banned - Rules violations

Banned - Rules violations

New Member

Banned - Rules violations

Banned - Rules violations

Banned - Rules violations

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock