Forum Discussion
Create new Table, filter empy values and concatenate values
Hi everyone,
I've recently connected a data source in PowerBI from a SharePoint List, which originates from a PowerApps form. The information I'm working with appears a bit disorganized as it's intended to record the attendance of participants in a meeting. Therefore, I'm looking to restructure it for better readability.
The data is presented across several columns, grouped by three main categories: NAME, COMPANY, and DEPARTMENT, each of which is followed by a numerical suffix (_01, _02, _03, etc.). To illustrate, please refer to the image below:
To streamline this data, I believe I need to create a new table. My plan is to filter out any missing values and then concatenate the columns to consolidate them into four distinct columns, as demonstrated in the following image:
Any guidance or suggestions on how to achieve this efficiently would be greatly appreciated. Thank you in advance for your assistance!
2 Replies
Assuming this is all one table, you can do this in your transform data step (Table View --> Transform Data (under the home tab). Select the columns you want as 1 (e.g. Name_01, Name_02, etc.) and unpivot these. This should get you all names under one column. Repeat for your other fields
Thank you for your help, but in my case I am having a I/O trouble with the data connection since I managed to load the information from the Sharepoint List, as mentioned here- But when I go to Power Query and try to unpivot the columns, an error shows and tells me "We were unable to authenticate you with the credentials provided. Try again".
