How do I create This cross table

[KH11NDR]

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

[Anonymous · Accepted solution]

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

[KH11NDR]

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.

[Anonymous · Accepted solution]

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"

[Anonymous · Accepted solution]

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