Set a date to a specific column

[Luca_Luca]

Hello, 

I would like to set a specific date to a column. 
I got 12 column, each column had specific data in (like the budget per product for each month). I am trying to set a date on each column so that for example, the column named "Budget January" is link to January and so when I do a visualisation with the "clustered column chart" I show the Sum of the budget (sum of each of the 12 column) for each month. None of the column had a date link to it, and I don't know how I can had a date for each column without adding a new line or column in my database to set the time. I am not sure if I explained my problem properly so feel free to ask me more details. 
Thanks for the help you will provide ! 

Best regards,
Luca

[v-jianboli-msft · Accepted solution]

Hi @Luca_Luca ,

Based on your description, I have created a simple sample:

Please try to unpivot the 12 columns:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XU9JEsAgCPsL5x4Kitq3OP7/G2VocLCHuJHEZE6qdJEYimEY+LalG9QQwwaC0rqmT0pQ2fDkB4V+4Cyu2bYl2dV0FsAzuETgzP90x6XBt7tmYNSAB656BArP+KWDXVP1yKdg8861W37970QWOoux72u9", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Budget January" = _t, #"Budget February" = _t, #"Budget March" = _t, #"Budget April" = _t, #"Budget May" = _t, #"Budget June" = _t, #"Budget July" = _t, #"Budget August" = _t, #"Budget September" = _t, #"Budget October" = _t, #"Budget November" = _t, #"Budget December" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Budget January", Int64.Type}, {"Budget February", Int64.Type}, {"Budget March", Int64.Type}, {"Budget April", Int64.Type}, {"Budget May", Int64.Type}, {"Budget June", Int64.Type}, {"Budget July", Int64.Type}, {"Budget August", Int64.Type}, {"Budget September", Int64.Type}, {"Budget October", Int64.Type}, {"Budget November", Int64.Type}, {"Budget December", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Then apply to the clustered column chart:

Final output:

If the result is not waht you wanted, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft · Accepted solution]

Hi @Luca_Luca ,

When you're editing a report in Power BI, you can choose a column to define the sort order of another column.

So you can try to create a new column of number for the month(january ->1 Feburary ->2 etc...) Then sort the attribute column by the new column.

For more details, please refer to:

Sort one column by another column in Power BI - Power BI | Microsoft Learn

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft · Accepted solution]

Hi @Luca_Luca ,

Based on your description, I have created a simple sample:

Please try to unpivot the 12 columns:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XU9JEsAgCPsL5x4Kitq3OP7/G2VocLCHuJHEZE6qdJEYimEY+LalG9QQwwaC0rqmT0pQ2fDkB4V+4Cyu2bYl2dV0FsAzuETgzP90x6XBt7tmYNSAB656BArP+KWDXVP1yKdg8861W37970QWOoux72u9", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Budget January" = _t, #"Budget February" = _t, #"Budget March" = _t, #"Budget April" = _t, #"Budget May" = _t, #"Budget June" = _t, #"Budget July" = _t, #"Budget August" = _t, #"Budget September" = _t, #"Budget October" = _t, #"Budget November" = _t, #"Budget December" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Budget January", Int64.Type}, {"Budget February", Int64.Type}, {"Budget March", Int64.Type}, {"Budget April", Int64.Type}, {"Budget May", Int64.Type}, {"Budget June", Int64.Type}, {"Budget July", Int64.Type}, {"Budget August", Int64.Type}, {"Budget September", Int64.Type}, {"Budget October", Int64.Type}, {"Budget November", Int64.Type}, {"Budget December", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Then apply to the clustered column chart:

Final output:

If the result is not waht you wanted, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Luca_Luca]

Hello, 

Thank you very much for the help !

That's exactly what I was trying to do. But is there a way to order the columns to be in month order ? Like first column in the chart is for January, then February then March etc... ? 

Thank you 

Best Regards,

Luca