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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
BT_19
New Member

Monthly trends and other visualization from weird dataset

Hi Team,

 

I have list of projects in column A and against which corresponding revenue captured for different months in different columns with headers Jan'23, Feb'23 etc (we have 3 years of data - month wise). Now even if I unpivot these columns, it does not help me build report on the same. I need to show the monthly trends and other DAX which will compare previous year to this year etc

1 ACCEPTED SOLUTION

Hi, @BT_19 

 

You can try the following methods. Select the Project Name column, and then select the Transform—Unpivot Other Columns:

vzhangti_2-1695111252602.png

vzhangti_3-1695111276470.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

5 REPLIES 5
WaqarArshad
Helper I
Helper I

You can use the below mquery to transform your data. This format will allow you to create trendlines and lookup YTD, MTD etc. 

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PVPJldxACM1FZw6sBRxtjz2TQ7/OPw0jQH2rJ5a/odfr+nXBRYIg6vdLFTL5fvEBD+xXwsnTfQeOSX8LMLWtknefECBrbwlgm1kBV+qqVV9PYIKKXW94Xb+7m8CsKxQQ2PtRC6l5EIJq7yIBTdk+Ps2IrVh2nwiYdB8KCOEicTYjdjBqHliziY3+ZxWjjGKFmB4VSBlGXH74VgVjGTmNHwouXSUD9nkRZMyrNKCvW06xfuCJRv9a9yTHvQSMxx/38fEUj+xq+Y3DyMGN9tvZWStuo5gfj7T6JslKN8fB4mur/W+j31zzk11zxcpdR+cB9QfJMfcqcNC1Ujy86DgslQHH+colRRb96FldR7PR/6264rtpm+eyDs2nezDvhDjXWz6Ptz4XUAmp5eYiewul/dD6rXMBdWVRam70797vVYl1RQ8uDx+Xua4u4kmIdPs4czUpx/ZJ9oRi3e6Thk7a5RH7kwbhoP80Urmyzte18lz651rRICzWRxyWtd/5+afEz26x2VKvlJmtTIdbKfbP/3jf7vv9Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Jan-21" = _t, #"Feb-21" = _t, #"Mar-21" = _t, #"Apr-21" = _t, #"May-21" = _t, #"Jun-21" = _t, #"Jul-21" = _t, #"Aug-21" = _t, #"Sep-21" = _t, #"Oct-21" = _t, #"Nov-21" = _t, #"Dec-21" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Jan-21", Int64.Type}, {"Feb-21", Int64.Type}, {"Mar-21", Int64.Type}, {"Apr-21", Int64.Type}, {"May-21", Int64.Type}, {"Jun-21", Int64.Type}, {"Jul-21", Int64.Type}, {"Aug-21", Int64.Type}, {"Sep-21", Int64.Type}, {"Oct-21", Int64.Type}, {"Nov-21", Int64.Type}, {"Dec-21", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Name"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Period"}, {"Value", "Revenue"}})
in
#"Renamed Columns"

Please suggest what action did we take in power query window as this code might work in one file and actual file might be different. Is this system driven code and then we tweaked it or this is entirely needs to be created manually.

BT_19
New Member

Hi,

 

I am unable to attach file here hence table is pasted here.

 

Project NameJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21
A $        130,347 $        144,992 $        126,780 $        129,696 $        136,653 $        128,545 $        129,170 $        131,024 $        148,250 $        123,741 $        135,175 $        109,435
B $        141,555 $        118,803 $        104,652 $        110,440 $        113,493 $        118,266 $        125,782 $        133,530 $        103,310 $        109,294 $        127,511 $        108,290
C $        144,032 $        124,811 $        143,935 $        112,997 $        124,308 $        110,716 $        134,737 $        115,277 $        111,987 $        114,607 $        128,718 $        123,068
D $        129,390 $        139,085 $        109,774 $        146,939 $        138,505 $        117,751 $        138,690 $        135,300 $        102,493 $        147,792 $        130,940 $        134,590
E $        121,989 $        131,027 $        106,748 $        116,471 $        138,709 $        126,051 $        149,462 $        135,039 $        142,010 $        125,933 $        130,646 $        109,649
F $        146,102 $        104,579 $        147,849 $        109,609 $        144,029 $        111,267 $        114,766 $        112,459 $        110,382 $        137,761 $        123,494 $        141,871
G $        107,498 $        134,460 $        104,705 $        120,488 $        129,314 $        134,299 $        130,428 $        120,395 $        140,800 $        135,418 $        149,273 $        147,101
H $        100,689 $        133,322 $        119,294 $        105,858 $        125,098 $        140,720 $        123,376 $        100,589 $        100,934 $        101,928 $        141,724 $        148,308

 

Thank you.

Hi, @BT_19 

 

You can try the following methods. Select the Project Name column, and then select the Transform—Unpivot Other Columns:

vzhangti_2-1695111252602.png

vzhangti_3-1695111276470.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

 

 

WaqarArshad
Helper I
Helper I

Hi,

 

Would you be able to share some data? 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.