Create new Table, filter empy values and concatenate values

[949184_soy]

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!

[ExcelMonke]

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

[949184_soy]

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".