cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KH11NDR Member
Member

How do I create This cross table

Hi Guys,

 

How do I create this cross table, Above excel table is how my data is and the below table is how i want it.

 

Thanks

 

cross table.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
NickNg278 Senior Member
Senior Member

Re: How do I create This cross table

@KH11NDR,

Copy and paste this code below in your blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEKKMrPSk0uUQBzDI2AhIkpkDAzU4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Project Name" = _t, #"02/01/2018" = _t, #"01/02/2018" = _t, #"01/03/2018" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", type text}, {"Project Name", type text}, {"02/01/2018", type text}, {"01/02/2018", type text}, {"01/03/2018", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project ID", "Project Name"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Date"}, {"Value", "Values"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}, {"Date.3", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Date.1", "Month"}, {"Date.2", "Day"}, {"Date.3", "Year"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Day"})
in
    #"Removed Columns"

View solution in original post

Moscuba Member
Member

Re: How do I create This cross table

I'd convert the data into a standard data format which will give you what you want (except year which can be a Measure or calculated column.

 

Import the data

Under Edit Queries Transform tab

Highlight the columns with trhe dates

Click on "Unpivot Columns"

 

View solution in original post

3 REPLIES 3
NickNg278 Senior Member
Senior Member

Re: How do I create This cross table

@KH11NDR,

Copy and paste this code below in your blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEKKMrPSk0uUQBzDI2AhIkpkDAzU4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Project Name" = _t, #"02/01/2018" = _t, #"01/02/2018" = _t, #"01/03/2018" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", type text}, {"Project Name", type text}, {"02/01/2018", type text}, {"01/02/2018", type text}, {"01/03/2018", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project ID", "Project Name"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Date"}, {"Value", "Values"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}, {"Date.3", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Date.1", "Month"}, {"Date.2", "Day"}, {"Date.3", "Year"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Day"})
in
    #"Removed Columns"

View solution in original post

Moscuba Member
Member

Re: How do I create This cross table

I'd convert the data into a standard data format which will give you what you want (except year which can be a Measure or calculated column.

 

Import the data

Under Edit Queries Transform tab

Highlight the columns with trhe dates

Click on "Unpivot Columns"

 

View solution in original post

Highlighted
KH11NDR Member
Member

Re: How do I create This cross table

Good thing about this forum, It takes a while to get a reply, so that makes me work it out 80% of the time before i get the answer.  It's so easy once you know how, I mean, this task is so easy now lol.

 

I used the unpivot table way, which if i'm write is the best and easiest way to do it.

 

Thanks Guys, I've rewarded you guys with some points.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 357 members 3,489 guests
Please welcome our newest community members: