Link entire column to a date

[SGpowerbi]

Hello everyone,

I have a table with a column for the name and a column for each month and I would like to get a stacked column chart with the sum of each month.

I'm able to get a chart but I don't know how to link each column to a date.

My exemple table :

 

 Thank you in advance,

[v-frfei-msft · Accepted solution]

Hi @SGpowerbi,

You can transpose your table in Power query by these steps as the picture.

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTVU0lECYSMwHasDETWCihqiiBoDeQZ6plBxEAsmYwISM4ATCC2mEC6MMIBLmEHtBGMkk8yBfGM4jo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"10/31/2018" = _t, #"11/30/2018" = _t, #"12/31/2018" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"10/31/2018", type number}, {"11/30/2018", Int64.Type}, {"12/31/2018", type number}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type date}, {"Name1", Int64.Type}, {"Name2", Int64.Type}, {"Name3", type number}, {"Name4", Int64.Type}, {"Name5", Int64.Type}, {"Name6", type number}, {"Name7", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Name", "Date"}})
in
    #"Renamed Columns"

Then you can create a date table and create relationship between them.

Table = CALENDARAUTO()

For more details, please check the pbix as attached.

Regards,

Frank