Import text as text field

[CKO]

Im looking for a way to make a report where my colleagues either in Excel or an other ideal program can type in some text comment for each their sector, and then have this automatically update/feed in to the report thats build in Power BI and then show it as a text field in the report with the text input.

This would work like a report that contain various key numbers it pull in from data resources, and once a week a memeber for each sector have to input their comment for the week somewhere that will then automatically feed into the report as text field/view.

Hope my explination above makes sense, and if this is possible ?

[edhans · Accepted solution]

I do this all ofthe time @CKO - however I recommend against Excel, or use it as a last resort. Excel's greatest strength in being able to do what you want with it is its greatest weakness for ingestion into Power BI. People will merge cells, type CTRL-ENTER returns in cells, and all kinds of things that will either mess the report up visually or break the refresh entirely. You can mitigate this with protecting fields, locking workbook settings, etc, but it is never foolproof.

I just implemented a report where users would key in comments for Purchase Orders with delivery comments - why is it late, what is the latest data from the vendor, etc. I used Lists in Teams for this, which is just a SharePoint list. You have total control over the data. A numeric field simply cannot accept data in SharePoint Lists. In Excel, if you paste info, you can bypass any kind of data validation restrictions. If you have Office 365 in your tenant, I strongly suggest a SharePoint List for this as they are easy to set up - and even easier through the Teams Lists app. Then you just connect to the List through Power Query by pointing to the SharePoint URL of the Team/SP site. 

Other options include Microsoft Forms, which puts a basic UI on an Excel file, but people can still get to the underlying Excel file. It is still better than a direct Excel file. You can get really fancy with a Power Apps interface and then you control even more than SharePoint Lists - but that is more work. In order of prefrence for me:

1. SharePoint List - super easy to use and set up.
2. Microsoft Forms - easy to set up, nice UI, but still has Excel underneath and can be an issue.
3. Power Apps on any datasource you want (Excel, SharePoint List, SQL Server, etc.)
4. Anythign else
5. Excel 😀

To make it work you just need a key field. In my example above. the user filled in 2 fields. The PO number - and this is what you merged with in Power Query to bring the data into the model, and a Comment field, which I think I set to 512 chars so they could write enough but not put an entire novel in there. The nice thing about a SharePoint list is it automatically tracks their user name, email address, add date/time, change date/time so you can add that metadata to the report too if desired. An Excel list would require the to fill all of that info out.

[Jimmy801]

Hello @CKO 

it makes sense and it's the reason why we have power query. When you design you process you just have to define what datasource you need. I think Excel it's the perfect solution. You can there establish some datachecks if needed, format it nicely... use tables, thats the optimum approach. Then define if everybody is making these comments in one file in one table or if every person needs his own file or sheet or whatever. Define the storage place where to collect and then read this data, uniting it and use it in your datamodel.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy