Can Power Bi Do this?

[Knight4hire]

Greetings;

I have two spreadsheets.

The spreadsheets list data for various offices from various States.

I need the two spreadsheets to match the data by state with office break down from both sheets and list it on one sheet.

to add up totals by State in each column.

to do a conditional format of certain columns.

In the past I would do this with a Macro in Excel.

I no longer am able to use Macros, thus it was suggeated to use Power Bi.
Can Power Bi do this?

[Knight4hire]

OK, I have posted two of the actual spreadsheets and the the third that is the report I need to make from the two spreadsheets.
It looks to me that Power Bi does not have the power to do what I need!

I am still waiting on an answer on this topic.

[Knight4hire]

Can Power Bi run a Macro like Excel did in the past?

[Knight4hire]

Here are the basic steps I would perform in an Excel Macro that I now need to have Power Bi accomplish:

Small Business Tab

Delete row 1 and 2

Delete column A

Change width of Column A to 34

Change Row height of row one to 85

Select row one, right click to format cell. Go to alignment tab and click on wrap text

Select all rows except the total row of the spreadsheet and sort by Column A

Copy Column B from State Code spreadsheet and paste it in column A

Select all rows except the total row of the spreadsheet and sort by Column A

SAT Tabt

                Delete column A

                Move row to the bottom of the data

                Delete the empty row

                Replace ‘USPFO ‘ in Column B to nothing

                Replace P&C‘ in Column B to nothing

                Replace ‘NGB-‘ in column ‘B’ to nothing

                Select all rows except the total row of the spreadsheet and sort by Column A

                Select columns C and D and pasted in Z and AA of Small Business Spreadsheet

Formatting Tab

                Copy row one and paste it in row one of the Small Business tab

Small Business Tab

                High light entire spreadsheet and double click to justify row width.

                Insert three rows below each State

                Total columns for each State

                Paste the exception rows for those States with Exceptions.

                Conditional format of columns F, I, R, U, X and Z

                Delete Conditional Formatting in the columns of the States that have exceptions

[Knight4hire]

This last screen shot is what the data will look like after the two spreadsheets are merged and formatted.

[Knight4hire]

[Knight4hire]

I have added a screen shot of the two spreadsheets.
The Macro I had would convert Column B in the second spreadsheet to match column B in the top spreadsheet.
it would then add colums D and E of the lower spreadsheet to the end on the top spreadsheet.
The macro would then sort the rows to group by State.
it would then add a total row under each State. then is would do a conditional formatting of certain columns.

[v-junyant-msft · Accepted solution]

Hi @Knight4hire ,

If you want to combine data from two tables into a single table by matching data, this is possible in Power BI, as long as there is some kind of association between the two tables.
If you want to do some kind of calculation on the data in the table, Power BI comes with the calculation function of the data, and if you want to implement some more complex calculations, you can also try to use the DAX function to create measures, calculate columns, and calculate tables to achieve the functions you want.
If you want to perform data processing on the data in a table, Power Query is present in Power BI to facilitate data transformation.

Would it be convenient for you to provide some sample data and the desired result renderings (without any privacy data)? You don't need to provide the original data, you just need to create some test data by yourself according to the format of the original data. In the case of sample data, it is more convenient for me to provide you with a solution.

Best Regards,

Dino Tao

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

[Knight4hire]

So do I need to format the data first before I use Power Bi to match up the columns?

[Knight4hire]

Greetings;
I have posted screen shots of the two spreadsheets and what I need the final spreadsheet to look like.

In the past I would have an Excel macro format the data of the second sheet so that the two sheets would a matching column of data.
This macro would then merge to data into one spreadsheet.
Then it would sort the data.  Add up totals for each group.  Do a conditional format of certain columns.

[Power5]

Can you please supply the data you are talking about in excel? Screenshots will do.

[Knight4hire]

How do I add a screen shot to this