Reformatting/Changing raw data summed in column/YTD format to row format

%3CLINGO-SUB%20id%3D%22lingo-sub-1775130%22%20slang%3D%22en-US%22%3EReformatting%2FChanging%20raw%20data%20summed%20in%20column%2FYTD%20format%20to%20row%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1775130%22%20slang%3D%22en-US%22%3EI%20get%20my%20raw%20data%20from%20a%20payroll%20provider%20in%20this%20format%20that%20is%20YTD%20with%20the%20months%20across%20the%20columns%2F%20summed%20across%20columns.%20I%20previously%20received%20my%20raw%20data%20the%20same%20way%20I%20did%20in%20the%20table%20to%20the%20right%20where%20each%20row%20was%20an%20instance%20of%20data.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20an%20easy%20way%20to%20achieve%20transforming%20my%20data%20from%20that%20%E2%80%9Ccolumn%20YTD%E2%80%9D%20format%20to%20a%20row%2Fper%20instance%20in%20rows%20format%3F%3CBR%20%2F%3E%3CBR%20%2F%3EJust%20to%20test%2C%20I%20did%20paste%20per%20person%2C%20then%20ACCT%2C%20then%20ACCT%20%23%2C%20and%20then%20the%20entire%20month%20column%20is%20pasted%20in%20the%20table%20in%20Total.I%E2%80%99ll%20add%20other%20data%20needed%20too.%20I%20have%20more%20than%203%2C000%20rows%20of%20data%20and%20have%20to%20do%20this%20every%20month.%20Is%20there%20a%20better%20option%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%E2%80%99m%20brand%20new%20to%20Power%20Query%20and%20doing%20a%20LinkedIn%20Learning%20course%20to%20master%20but%20is%20there%20a%20way%20to%20get%20that%20data%20in%20the%20format%20needed%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1775130%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1775458%22%20slang%3D%22en-US%22%3ERe%3A%20Reformatting%2FChanging%20raw%20data%20summed%20in%20column%2FYTD%20format%20to%20row%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1775458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830577%22%20target%3D%22_blank%22%3E%40jpalaci22%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20Power%20Query%20shall%20work%20especially%20if%20you%20receive%20your%20data%20as%20Excel%2Fcsv%2Ftext%20file.%20When%20query%20the%20source%20and%20unpivot%20on%20columns%20with%20months.%20Sorting%20by%20proper%20months%20order%20will%20require%20some%20manipulation%20and%20not%20clear%20from%20here%20year%202020%20is%20taken.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20your%20source%20data%20have%20exactly%20the%20same%20format%20%2F%20layout%20as%20in%20your%20sample%20file%20and%20is%20it%20in%20separate%20file%20or%20not%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1776752%22%20slang%3D%22en-US%22%3ERe%3A%20Reformatting%2FChanging%20raw%20data%20summed%20in%20column%2FYTD%20format%20to%20row%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1776752%22%20slang%3D%22en-US%22%3EHi%20and%20thank%20you%20for%20helping%20out.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20file%20is%20a%20sample%20with%20random%20data.%20But%20it%20comes%20in%20that%20data%20set%20format%20on%20the%20left%20with%20the%20table%20the%20end%20result%20I%20need%20it%20in.%3CBR%20%2F%3E%3CBR%20%2F%3EIt%E2%80%99s%20a%20little%20busy%20for%20me%20to%20power%20through%20the%20Power%20Query%20LinkedIn%20Learning%20course%20and%20I%20really%20want%20to%20understand%20it%20so%20I%E2%80%99m%20going%20to%20make%20time%20for%20it%20now%20but%20I%20need%20this%20data%20in%20this%20format%20for%20now.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
I get my raw data from a payroll provider in this format that is YTD with the months across the columns/ summed across columns. I previously received my raw data the same way I did in the table to the right where each row was an instance of data.

Is there an easy way to achieve transforming my data from that “column YTD” format to a row/per instance in rows format?

Just to test, I did paste per person, then ACCT, then ACCT #, and then the entire month column is pasted in the table in Total.I’ll add other data needed too. I have more than 3,000 rows of data and have to do this every month. Is there a better option?

I’m brand new to Power Query and doing a LinkedIn Learning course to master but is there a way to get that data in the format needed?
3 Replies
Highlighted

@jpalaci22 

In general Power Query shall work especially if you receive your data as Excel/csv/text file. When query the source and unpivot on columns with months. Sorting by proper months order will require some manipulation and not clear from here year 2020 is taken.

 

Is your source data have exactly the same format / layout as in your sample file and is it in separate file or not?

Highlighted
Hi and thank you for helping out.

This file is a sample with random data. But it comes in that data set format on the left with the table the end result I need it in.

It’s a little busy for me to power through the Power Query LinkedIn Learning course and I really want to understand it so I’m going to make time for it now but I need this data in this format for now.
Highlighted

@jpalaci22 

Generate by Power Query table is here

image.png

entire columns with source data are named as Range. Idea as in previous post.