Monthly trends and other visualization from weird dataset

[BT_19]

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

[WaqarArshad]

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"

[BT_19]

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]

Hi,

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

Project Name	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-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.

[v-zhangti · Accepted solution]

Hi, @BT_19 

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

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]

Hi,

Would you be able to share some data?