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

sandy666 · Jul 24, 2020

You are welcome

btw. long time no see, hope all is well

last time here

Mendy32 · Jul 24, 2020

Yes, it's been a while...I hope you are doing well.

Wait...I have a few dups..I need to group by Name and DOB..no need to include admit date

Mendy32 · Jul 24, 2020

as you can tell...i'm still learning.

sandy666 · Jul 24, 2020

I was too fast

try this

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"),
    Distinct = Table.Distinct(TypeDates, {"I.d #", "Name", "DOB", "RAE", "SEX", "ADMIT"}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Distinct, {{"Dates", type text}}), List.Distinct(Table.TransformColumnTypes(Distinct, {{"Dates", type text}})[Dates]), "Dates", "Level")
in
    Pivot

7848 looks like duplicate but in Admit you have different dates so this is not duplicate

if you don't care about ADMIT dates use:

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"),
    Distinct = Table.Distinct(TypeDates, {"I.d #", "Name", "DOB", "RAE", "SEX"}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Distinct, {{"Dates", type text}}), List.Distinct(Table.TransformColumnTypes(Distinct, {{"Dates", type text}})[Dates]), "Dates", "Level")
in
    Pivot

sandy666 · Jul 24, 2020

or this one

Rich (BB code):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"DOB", type date}, {"ADMIT", type date}, {"Dates", type date}}, "en-US"),
    Distinct = Table.Distinct(Type, {"I.d #", "Name", "DOB", "RAE", "SEX"}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Distinct, {{"Dates", type text}}), List.Distinct(Table.TransformColumnTypes(Distinct, {{"Dates", type text}})[Dates]), "Dates", "Level")
in
    Pivot

Mendy32 · Jul 26, 2020

I keep getting this error message..

Expression.Error: The specified distinct criteria is invalid.
Details:

Mendy32 · Jul 26, 2020

Expression.Error: The specified distinct criteria is invalid.
Details:

= Table.Distinct(TypeDates, {"I.d #", "Name", "DOB", "RAE", "SEX", "ADMIT"})

sandy666 · Jul 26, 2020

try

Rich (BB code):

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

Mendy32 · Jul 26, 2020

let me try..

sandy666 · Jul 26, 2020

sure, hope you didn't change any headers in source table (PQ is case sensitive)

Power query question - dates into columns

Mendy32

New Member

sandy666

Banned - Rules violations

Excel Facts

Mendy32

New Member

Mendy32

New Member

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

Mendy32

New Member

Mendy32

New Member

sandy666

Banned - Rules violations

Mendy32

New Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

Power query question - dates into columns

New Member

Banned - Rules violations

Excel Facts

New Member

New Member

Banned - Rules violations

Banned - Rules violations

New Member

New Member

Banned - Rules violations

New Member

Banned - Rules violations

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