Creating a Macro/formula

Brass Contributor

Hello!  I have a bit of a problem... I have a file with all of my data in it, but it's not friendly for PowerBI input... I was wondering if there is a way to create a macro/formula to change the format?

 

I had a request earlier, but it wasn't able to refresh.  

 

Any advice/tips on how to combine this data is MUCH appreciated!  I would really love to not have to manually manipulate each by hand.

 

I've attached the report - but not sure how to create it into a format like this:

nattiej101_0-1706722739037.png

 

 

2 Replies

@nattiej101 

See the attached workbook for one incomplete-but-useful solution. Start by reading the contents of the _Info worksheet.

 

Additional note: I changed the Freeze Panes settings on the original worksheets to make it easier to review the data.  You may change those settings back if desired.

While the attached solution from SnowMan55 provides a helpful start, I wanted to offer some additional suggestions as well. Consider using Power Query in Excel to transform the data. It allows you to automate many cleansing tasks to reshape, split, filter, and combine data for analysis. For frequent refresh needs, importing the data directly into Power BI Desktop may work better long-term rather than using Excel intermediaries.

Power BI has data connectivity and transformation features to handle large volumes. If working with CSV/text files, the Power BI Text/CSV connector can parse those quickly while handling inconsistencies.