SEVERELY STUCK , How to Split Multiple Columns into Row?

[Chanleakna123]

D

Dear All , 

Can you share me your best Practice how to splits names in each columns into Row ? 
i have 3 columns need to be splited at the same time. 

your input is really appreciated. 

Thx 

[v-danhe-msft]

Hi @Chanleakna123,

Due to I could not figure out the name in your columns, could you please post me the file or picture with english?

Regards,

Daniel He

[Chanleakna123]

hi @v-danhe-msft

Those are name : 

For ex:

HANG CHANLEAKNA 

BE CHHAYRATH 

KHUN SOPHEA 

KEAN VANNY

EM DANY 

SOK CHANTHA

.............................. 

.............................. 

.............................. 

So i have around 89 name in one row. 

I can't use any split method over this. those doesn't work. 

not sure how to settle this. 

[Seward12533]

In power Query - and there are a few ways

Method 1 - both of these commands are available from the Ribbon and have wizards and help

1. Split Column using "," as seperator be sure to look under advanced options and choose every occourance and choose a number of colums larger than the maximum number of possible items
2. Unpivot these columns

Mehod 2 -  This will dynamically adjust to the maximium number or columsn and be future proof if more are added later but requires some M that I dont' think you can get from the ribbon

1. convert the comma separated list into a "List" Object,
   1. Add a custom Column
   2. enter this formula  where [List of Values] if your column containing comma separated list. Wrapping it in {} converts it to a list and then the Table.FromList converts it to a table

      Table.FromList({[List of Values]}))

2.  then exapand it using the expand icon on the top right of the column header of column contianing the table
3. delete the extra columns that were added.

Here is the M Code - you can create a new query from "Blank Query" and then past it in. you can then see how it works step by setp. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcqxEYAwDATBXj6+QC+ZArDL0Kj/Nhgg3dlu3UImKRaXhtZ+JXHhhX8733KQQYVmHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Row = _t, #"List of Values" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromList({[List of Values]})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Custom", {"Row", "List of Values"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"List of Values", "Attribute"})
in
    #"Removed Columns"

[Chanleakna123]

HI @Seward12533 sorry for inconvenient caused , i failed to get it , since this is such advance method one. 

i love the second method , but wait , i have 3 columns needed to be seperated into rows. 
Can you show me the guidline step by step ? and i also couldn't find expand button. 

Sorry i'm very basic here 😞 

Thx 

[Seward12533]

Need to do it for each of your columns - tho ideally you should try to find or fix the data source if possible.

Here is walkthrough

List of ValueAdd Custom ColumnAdd M to convert to tableClick Up Arrow / Angel Wing iconExpand ALL (default) - so hit okSelect all OTHER columns than the ones expanded and UNPIVOT OTHER ColumnsSelect the Old List of Values Column and Attribute Column and delete themVoilla- Rinse and Repeat

[Chanleakna123]

HI @Seward12533 

I tried it , but not as expectation. 
I'm not sure what happen , but 1 row i have around 100 Name , it might be quite big , and i tried manual by using split delimeters also failed to acheive it. would you mind to let me know other method to proceed it. 
i'm so stuck with this report. And this raw data comes from what users had been filled from app. 

let me share you the link and you can try it . 

https://docs.google.com/spreadsheets/d/e/2PACX-1vQNqEBQo9v31OleVGCQYG1AIFwhw-YnDJ7P6fDEukxMsFIqAqfN5...

 

[Seward12533]

Can you save the PBX file or Excel vs HTML and share that I could not really use the file.  But should not matter if you have 100 you will get 100 rows for that 1 row.   The special characters may be causing the M some trouble but I'm not sure.