Importing data with based on rolling set of months

[arolon2]

Hello,

I have folder with years of excel reports that provide estimate projections 24 months into the future for specific items. I would like to mass import them but understand the difficulties due to the the column headers changing over time. I could import the report one at a time and unpivot the months colunms but that is too cumbersome. Is Power Query able to automate this unpivot step so that the data can be combined when bringing in the folder of reports?

[amitchandak]

@arolon2 , once you define steps in power query they should repeat with new data load.

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[arolon2]

That's where I'm stuck. If I try to import the folder in Power query not all the data is imported due to column header mismatching. I am unsure how to have the unpivot step happen as the data is brought in to the model.

[v-lionel-msft]

Hi @arolon2 ,

" If I try to import the folder in Power query not all the data is imported due to column header mismatching. "

Like this?

You may need to modify the column headings in the data source and then import the folder.

Best regards,
Lionel Chen

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

[arolon2]

Let me provide my own visual for better explination. For example I have a report that shows the following:

Item	Item Desc	Item Family	Feb	Mar	Apr	May
Stuff	Stuff	Stuff	#	#	#	#

The # are future projection, the report is ran each month so next period it appears like so:

Item	Item Desc	Item Family	Mar	Apr	May	Jun
Stuff	Stuff	Stuff	#	#	#	#

What I am looking for is to combine the reports but as the column headers change an error gets tripped. I could import,  unpivot the table to look as follows, then append... but that would be too cumbersome. 

Item	Item Desc	Item Family	Month	Value
Stuff	Stuff	Stuff	Mar	#
Stuff	Stuff	Stuff	Apr	#
Stuff	Stuff	Stuff	May	#
Stuff	Stuff	Stuff	June	#

How do I get power query to do this unpivot step in the "sample file" step of importing?

[v-lionel-msft]

Hi @arolon2 ,

You can create a matrix visual and add [Month] to the ‘Column’ label.

Best regards,
Lionel Chen

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

[mhossain]

Hi @arolon2 

For Unpivoting part -

1). Go to powerquery and select first four columns (item, item,desc,family etc) and don't touch month columns.

2). Go to Transform tab in the toolbar -->  when you click 'Unpivot Columns' dropdown , choose second option "Unpivot Other columns" I think this will solve the issue when new months get added to the data.

Please let me know if above helps.

I guess you will have just one file at the end right, you dont require to combine the multiple excel files, right?

[mahoneypat]

Did you select Combine & Transform to combine all the files?  If so, there will be a Transform Sample File query (and an associated function).  If not already, you could do an Unpivot Other Columns step in that (assuming the columns not unpivotted are the same), so that when combine they all have Attribute/Value (or whatever you call the two columns from the unpivot step).

If needed, you can also use the approach shown here to dynamically change column names based on column # vs the actual names.  The video is in Power Query in Excel, but the approach/syntax is the same.

https://www.youtube.com/watch?v=yBJr0sAc-m4

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat