[Help] Getting started with the right tool? - Power Query, PowerBI, Transform Data

[PatrickG2020]

Hello, everyone,

I have the pleasure of familiarizing myself with the new Power Tools from Microsoft. I am completely at the beginning and am ready to familiarize myself with the topics.

the following concerns:

I get an Excel spreadsheet from service provider 1. This Excel spreadsheet is very large and has long headers.

I should compress this table for service provider 2 to the bare essentials.

This means that headings have to be changed. And from different cells it is necessary to calculate an average and write it into the new table.

The tool must recognize from the header of the source that it is reading values ​​from the columns below and should transfer them to a new table in transformed form.

Example:

Header A1 "TEXT1". (EXAMPLE)

Header A1 in the newly created table should be "T1".

Using a mapping table, numerical values ​​are to be output instead of words. The numerical values ​​in the cells should then be recognized from the header.

So many complicated processes.

We are currently doing this manually. We want to automate these processes.
First semi-automatic.

And in the future completely automatically in the cloud.

Can I first build such processes completely with Power Query or maybe already directly in PowerBI?

PowerBI Desktop and current Office365 apps are available.

I would be happy if you have an idea of ​​where I can start and which tool I can best use to map the entire workflow.

I thank you in advance 🙂

[PatrickG2020]

Hello,

I apologize for the late feedback.

The 2 tables are actually 2 different situations.

First situation:

We get Excel spreadsheets.
There are texts in the header.

These headers should be replaced by abbreviations.

For example "F1", "F2" etc. Ideally we have a mapping table with "Old Text" and "New Abbreviation"

There is also text in the cells of this table. "Yes" or "No".

In the newly created table with the new headers, the texts "Yes" or "No" should then be replaced by numbers from a mapping table.
If the header is "F1" it should be "2" instead of "Yes".
If the header is "F3" it should be "0.5" instead of "Yes".
With "no" "0" comes in everywhere.

That means Excel has to recognize the header, recognize the content of the cell and then write the new values ​​based on the mapping table or conditions.

In the second situation we get a table.
It contains a maximum of 7 test results. But it's not always 7. It can also be 3 or 5.
In the new table, these test results should be summarized in an average.
However, the results in the source are horizontal.

So Excel has to recognize the headers again and then pick up the values ​​in the cells and output the mean value in a new table.

I really hope i wrote it good 🙂

Thank you for your feedback 🙂

[AlexisOlson]

This sounds possible with Power Query but I don't fully understand what the input and expected output are or if/how your two images are related. Can you clarify what exactly you're starting with and what you expect to get as a result?