How to refer to the text in a specific excel cell in Card function

[Hengqiiiiii]

Hi, what I want to do is to create a report which shows the text value from excel file. Can anyone help me to achieve the idea?

for example, I would like to create a similar format to this excel worksheet in power bi, which can show the text result of each category. Is there any method that we can use Card function to show the text? 

Thanks so much!

[SteveCampbell]

As stated, it is not a good idea to have merged cells. Power BI will place the value in the first cell of the merged cells. 

For this worksheet, you could do the following steps:

1. Import as Excel.
2. Click "Edit", or go to the advanced query editor and open the query.
3. Delete all steps on the "Applied Stpes" so that you are left with just source.
4. Select all Columns. On "Transform" Tab, apply "Fil Down"
5. On "Home" Tab, apply "Remove Rows" > "Remove top Rows" and type 1.
6. On "Transform" Tab, apply "Use First Row as Headers". You may want to rename so you can differentiate Material and Water
7. Select all Columns. On "Home" Tab, apply "Remove Rows" > "Remove Duplicates"

Here, this will give you a format compatible with Power BI, and a line for each combination. This will allow you to construct a report that can display text from a card, or simply put all in a table.

[Johanno]

Not sure if I understand, but if you to see the same structure as in Excel you add a table and select all columns that you want. Then you can add a slicer if you just want to see one category. I think we need to see more of the table structure and the expected outcome.

[Hengqiiiiii]

Thanks so much for your reply. The image is from excel report. All the values or texts in the yellow area will change after I change parameters in another worksheet. I would like to have a similar report in PowerBI with Card Function, so that I can modify the shapes and sizes of boxes, and rearrange them to have the same format as this excel report. For now, I do not know how to link the card function with individual excel cell. For example, I want to make a card link to and show the value in cell D3, "Energy Consumed (e)", could you please teach me how I can achieve this result?

[Johanno]

Hi again, I see now that you have merged cells with different sizes. This is not a Power BI-friendly format. When you import merged cells only the top will remain and the other will be null.

I think we need a new structure of the source or maybe Power BI is not the right application for this or we need a Power Query ninja (which I'm not unfortunately). Hope someone else can help.