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"
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"

 

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"
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"

 

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
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 235 members 2,550 guests
Please welcome our newest community members: