Aligning split columns

Hi Anonymous 

I would recommend you

1. Split the values into a list contained within the column (not expanded into separate columns)
2. Convert each list to a record with field names equal to the values themselves
3. Expand the record columns

Here is some sample M code to illustrate:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WStRJ0klWitWJVkoGslLArBSdRJBYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    SplitToList = Table.TransformColumns( #"Changed Type", {{"Text",  Splitter.SplitTextByDelimiter(","),type {text}}} ),
    ConvertToRecord = Table.TransformColumns(SplitToList, {{"Text", each Record.FromList(_,_)}}),
    #"Expanded Text" = Table.ExpandRecordColumn(ConvertToRecord, "Text", List.Distinct(List.Combine(SplitToList[Text])))
in
    #"Expanded Text"

Regards

Owen 

Hi,

You may download my PBI file from here.

Hope this helps.

Hi Anonymous 

OwenAuge and Ashish_Mathur has shown you the way in PowerQuery Editor, and you can try my way by dax.

I use calculated columns to achieve your goal, after I split the value.

And I add an index column to sort the new columns.

A = IF('Table'[Column1.1]="a" ||'Table'[Column1.2]="a"||'Table'[Column1.3]="a","a",BLANK())

B = IF('Table'[Column1.1]="b" ||'Table'[Column1.2]="b"||'Table'[Column1.3]="b","b",BLANK())

C = IF('Table'[Column1.1]="c" ||'Table'[Column1.2]="c"||'Table'[Column1.3]="c","c",BLANK())

D = IF('Table'[Column1.1]="d" ||'Table'[Column1.2]="d"||'Table'[Column1.3]="d","d",BLANK())

Result:

You can download the pbix file from this link: Aligning split columns

Best Regards,

Rico Zhou

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