Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Luca_Luca
Frequent Visitor

Set a date to a specific column

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

2 ACCEPTED SOLUTIONS
v-jianboli-msft
Community Support
Community Support

Hi @Luca_Luca ,

 

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

vjianbolimsft_0-1676257562390.png

Please try to unpivot the 12 columns:

vjianbolimsft_1-1676257615996.png

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:

vjianbolimsft_2-1676257674744.png

Final output:

vjianbolimsft_3-1676257694115.png

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.

View solution in original post

v-jianboli-msft
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
v-jianboli-msft
Community Support
Community Support

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

Hi @Luca_Luca ,

 

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

vjianbolimsft_0-1676257562390.png

Please try to unpivot the 12 columns:

vjianbolimsft_1-1676257615996.png

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:

vjianbolimsft_2-1676257674744.png

Final output:

vjianbolimsft_3-1676257694115.png

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.

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 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.