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

Mendy32 · Jul 26, 2020

my goal was track if the levels changed per week. I know the first few months a lot of H levels were missing but May, June and July should be more complete to see a pattern..

I.d #	Name	DOB	RAE	SEX	ADMIT	3.9.2020	3.16.2020	3.25.2020	5.27.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	H0	H0	H1POS

Mendy32 · Jul 26, 2020

For example...this person level has not changed...

I.d #	NAME	DOB	RAE	SEX	ADMIT	Level	Dates
667	Ken and Barbie	1/2/1972	White	Male	10/31/2019	H0	5.20.2020
667	Ken and Barbie	1/2/1972	White	Male	10/31/2019	H0	5.27.2020
667	Ken and Barbie	1/2/1972	White	Male	10/31/2019	H0	6.3.2020
667	Ken and Barbie	1/2/1972	White	Male	10/31/2019	H0	6.10.2020
667	Ken and Barbie	1/2/1972	White	Male	10/31/2019	H0	6.17.2020
667	Ken and Barbie	1/2/1972	White	Male	10/31/2019	H0	6.24.2020
667	Ken and Barbie	1/2/1972	White	Male	10/31/2019	H0	7.1.2020
667	Ken and Barbie	1/2/1972	White	Male	10/31/2019	H0	7.8.2020
667	Ken and Barbie	1/2/1972	White	Male	10/31/2019	H0	7.15.2020
667	Ken and Barbie	1/2/1972	White	Male	10/31/2019	H0	7.22.2020
667	Ken and Barbie	1/2/1972	White	Male	10/31/2019	H0	5.13.2020

This person Level changed in April from H2 to H0

I.d #	NAME	DOB	RAE	SEX	ADMIT	Level	Dates
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H2	3.9.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H2	3.16.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H2	3.25.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H2	4.1.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H2	4.8.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	4.15.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	4.22.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	4.29.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	5.6.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	5.20.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	5.27.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	6.3.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	6.10.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	6.17.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	6.24.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	7.1.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	7.8.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	7.15.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	7.22.2020
589	Chucky	2/17/1997	Black or African American	Male	3/2/2020	H0	5.13.2020

sandy666 · Jul 26, 2020

did the last M-code works for you?

Mendy32 · Jul 26, 2020

No its giving me alot of blanks

Mendy32 · Jul 26, 2020

do you understand my example...????

sandy666 · Jul 26, 2020

i'd like to see original structure with generic data
in post#22 it looks like two different tables
maybe create example of your source data and expected result then share excel file via googledrive, dropbox, onedrive or any similar and post link here

Mendy32 · Jul 26, 2020

my data looks like post 22 but I want it to look like post 21...I'm look for the dates to become headers....

I hope I did it correctly..

I.d #	Name	DOB	RAE	SEX	ADMIT	3.9.2020	3.16.2020	3.25.2020	4.1.2020
6858	Wonder Woman	9/15/2000	PINK	Male	2/16/2020	H0	H0	H0	H1POS

Levels.xlsx

sandy666 · Jul 26, 2020

like this?



I.d #	NAME	DOB	RAE	SEX	ADMIT	5.20.2020	3.16.2020	4.15.2020	4.29.2020	7.1.2020	4.1.2020
278	ACEVEDO, CESAR	24/11/1984	Hispanic	Male	03/12/2019	H1POS
607	ACEVEDO, MARK ANTHONY	24/06/1994	Hispanic	Male	09/02/2020		H1
5824241	ACOSTA, ADRIANA	15/06/1996	Hispanic	Female	16/12/2019						H0
5896584	ACOSTA, ADRIAN	30/01/2002	Hispanic	Male	11/04/2020			H0
5920675	ACOSTA, ADRIAN	18/04/1996	Hispanic	Male	25/04/2020				H1
5960673	ACOSTA, ADRIAN	18/04/1996	Hispanic	Male	25/06/2020					H0

sandy666 · Jul 26, 2020

or



I.d #	NAME	DOB	RAE	SEX	ADMIT	5.20.2020	5.27.2020	6.3.2020	6.10.2020	6.17.2020	6.24.2020	7.1.2020	7.8.2020	7.15.2020	7.22.2020	5.13.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
278	ACEVEDO, CESAR	24/11/1984	Hispanic	Male	03/12/2019	H1POS	H1POS	H1POS	H1POS	H1POS	H1POS	H1POS	H1POS	H1POS	H1	H1POS
607	ACEVEDO, MARK ANTHONY	24/06/1994	Hispanic	Male	09/02/2020	H1	H1	H1	H1	H1	H1	H1	H1			H1	H1	H1	H1	H1	H1	H1	H1	H1
5824241	ACOSTA, ADRIANA	15/06/1996	Hispanic	Female	16/12/2019														H0	H0	H0
5896584	ACOSTA, ADRIAN	30/01/2002	Hispanic	Male	11/04/2020	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0	H0					H0	H0	H0	H0
5920675	ACOSTA, ADRIAN	18/04/1996	Hispanic	Male	25/04/2020																		H1
5960673	ACOSTA, ADRIAN	18/04/1996	Hispanic	Male	25/06/2020							H0	H0	H0	H0

s666-PQ-Levels.xlsx

Microsoft Excel Workbook

1drv.ms

Mendy32 · Jul 26, 2020

yes...how did you do it. i have over 25000 entries..

Power query question - dates into columns

Mendy32

New Member

Mendy32

New Member

Excel Facts

Mendy32

New Member

sandy666

Banned - Rules violations

Mendy32

New Member

Mendy32

New Member

sandy666

Banned - Rules violations

Mendy32

New Member

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

s666-PQ-Levels.xlsx

Mendy32

New Member

Similar threads

Forum statistics

Share this page

Power query question - dates into columns

New Member

New Member

Excel Facts

New Member

Banned - Rules violations

New Member

New Member

Banned - Rules violations

New Member

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