Can I get multiple columns from a querie to be added to a new column?

[Lucifer2019]

Hi, I have a querie table that has one row for each facility and date.  In the row are multiple columns that have a reference number.  Can I somehow combine the columns with these reference numbers into one column, that then lists all the other data in the other columns in the table?   I need to have each reference number be in its own cell in the column.  

I have 4 fields that create 15 columns each, so I will need to be able to join all this back up.  If I have to split it out into individal tables after its ingested into Power BI that is fine, as long as we can find a way to ink it all back up by station and date

There may or may not be info in the additional IR # fields. 

[v-rongtiep-msft]

Hi  @Lucifer2019 ,

Could you please tell me do you want to merge the columns? How about using merge? 

In Power Query, you can merge two or more columns in your query. You can merge columns to replace them with one merged column, or create a new merged column alongside the columns that are merged. You can only merge columns of a Text data type.

More details: Merge columns (Power Query) 

Please refer to the follwoing topic to see if it helps you.

Combining rows based on unique id, and combining information 

If I have misundestood your meaning, please provide your pbix file without privacy information and desire output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lucifer2019]

HI, I'm not trying to merge the colums, I have to be able to see each IR individually.  I need to take a row, with data in coulmn a-f and thne make that become, one column with the result being row 1-6.  I need to keep the identifying data to be able to filter in the visual by locatoin and date.  

OK, I was able to ingetst the data tale a 2nd time and remove a bunch of columns from the table and I was finally able to get it to unpivot.  I renamed the columns that I ned to all be in one column, I just need to get them to get back into a row format where the guid is the common link and I can join that new table with the larger table that has more reporting in it. 

This is an example of one of the lines that I was able to unpivot.  I have 3 IR numbers here that originally showed IR_1, IR_2, IR_3, Now they all have the same name so they should be in one column.  I can do the same with the other similar columns that I need to get in the like columns.  This will be a data source refreshed daily with new infomation inputted the previous day

https://1drv.ms/x/s!Au05W8T6kS8XgW7aM78wD8pHoWly?e=lQMXgM 

https://1drv.ms/u/s!Au05W8T6kS8XgW_Qhcjhbdgb_d9r?e=zWAHuZ

Attribute	Value
FileSystemObjectType	0
Id	7
ServerRedirectedEmbedUrl
ID	7
ContentTypeId	0x0100CDC6D05EF07AEA43A099158AB0BD503B000F300716211E4E41A7B3E1C27B9702D0
Modified	4/8/2022 16:00
Created	3/21/2022 18:29
AuthorId	2789
EditorId	3012
OData__UIVersionString	7
Attachments	FALSE
GUID	1df7b3c9-b328-450c-8c3a-9ef6a59df1b1
Report Date	3/9/2022 5:00
VA Police Station	Erie VA Police
IR_Classification	38 CFR 1.218(B) : (14) GAMBLING-PARTICIPATING IN GAMES OF CHANCE FOR MONETARY GAIN OR PERSONAL PROPERTY; THE OPERATION OF GAMBLING DEVICES-A POOL OR LOTTERY; OR THE TAKING OR GIVING OF BETS
Station ID	10N4
IR Number	1q
IR Number	2
IR Number	3
IR Number	444

Message 4 of 4

4 Views

0

Reply

Accept as Solution

[Lucifer2019]

OK, I was able to ingetst the data tale a 2nd time and remove a bunch of columns from the table and I was finally able to get it to unpivot.  I renamed the columns that I ned to all be in one column, I just need to get them to get back into a row format where the guid is the common link and I can join that new table with the larger table that has more reporting in it. 

This is an example of one of the lines that I was able to unpivot.  I have 3 IR numbers here that originally showed IR_1, IR_2, IR_3, Now they all have the same name so they should be in one column.  I can do the same with the other similar columns that I need to get in the like columns.  This will be a data source refreshed daily with new infomation inputted the previous day

https://1drv.ms/x/s!Au05W8T6kS8XgW7aM78wD8pHoWly?e=lQMXgM 

https://1drv.ms/u/s!Au05W8T6kS8XgW_Qhcjhbdgb_d9r?e=zWAHuZ

Attribute	Value
FileSystemObjectType	0
Id	7
ServerRedirectedEmbedUrl
ID	7
ContentTypeId	0x0100CDC6D05EF07AEA43A099158AB0BD503B000F300716211E4E41A7B3E1C27B9702D0
Modified	4/8/2022 16:00
Created	3/21/2022 18:29
AuthorId	2789
EditorId	3012
OData__UIVersionString	7
Attachments	FALSE
GUID	1df7b3c9-b328-450c-8c3a-9ef6a59df1b1
Report Date	3/9/2022 5:00
VA Police Station	Erie VA Police
IR_Classification	38 CFR 1.218(B) : (14) GAMBLING-PARTICIPATING IN GAMES OF CHANCE FOR MONETARY GAIN OR PERSONAL PROPERTY; THE OPERATION OF GAMBLING DEVICES-A POOL OR LOTTERY; OR THE TAKING OR GIVING OF BETS
Station ID	10N4
IR Number	1q
IR Number	2
IR Number	3
IR Number	444

[Lucifer2019 · Accepted solution]

So I was able to find a solution to the problem.  I added repetition of the querie table and then edited the columns names to match what the exsiting columns was named in the first table.  Then I use merge queries as new option and the system created a new table with the information in the correct columns, and not merged into one field.  Make sure you edit each of your additional tables with the colunn names before you do the merge and then it will all flow