Reply: Split a single row into multiple rows based on multiple colums

Power Query M Code Showcase
1

Looks like I can’t reply to topics either now…
@d.chek hope you get this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoLABKOQSEgMhhIBARHKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PIDM = _t, #"College 1" = _t, #"Dept 1" = _t, #"College 2" = _t, #"Dept 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PIDM", Int64.Type}, {"College 1", type text}, {"Dept 1", type text}, {"College 2", type text}, {"Dept 2", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"College 1", "Dept 1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"College-Dept-1"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"College 2", "Dept 2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"College-Dept-2"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"PIDM"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Value.1", "College"}, {"Value.2", "Dept"}})
in
    #"Renamed Columns"

d chek eDNA.pbix (18.7 KB)

1 Like
2

@jamie.bryan This worked like a champ and is exactly what I needed. Thanks so much for the quick reply it is very much appreciated.

Dan

3

@jamie.bryan I need to add a second dept field for each of the merged column, but still need to be listed under dept. So I need College 1, Dept 1, Dept 2, but I need both Dept 1 and Dept 2 to be listed under Dept. The second dept would be just another row but fall in the Dept column.

PIDM College1 Dept1 Dept2 College2 Dept1 Dept2
1 VP ART ATY AS ENG HIS

would need to be
PIDM College Dept
1 VP ART
1 VP ATY
1 AS ENG
1 AS HIS

Basically the idea is that I have one slicer for college, dept, etc. This then needs to filter the rows where as whatever they select I need to look in both College fields for colleges and the 4 dept fields for dept to select the record. I don’t want to have a hierarchy slicer where they have to select dept1 and dept2.

Any suggestions.

Thanks,
Dan

4

Can you provide an example of what you currently have and the desired output please.

Thanks,

5

just updated it

6

Here you go. Same premise, just duplicated the college columns first so we have 2 copies of each to combine with each dept before we pivot.

Can probably do in less steps, probably with customer columns etc. but it’s too hot for me to think that logically currently!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoLABKOQSEgMiQSRAYDCVc/dyDp4RmsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PIDM = _t, #"College 1" = _t, #"Dept 1" = _t, #"Dept 2" = _t, #"College 2" = _t, #"Dept 3" = _t, #"Dept 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PIDM", Int64.Type}, {"College 1", type text}, {"Dept 1", type text}, {"College 2", type text}, {"Dept 2", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "College 1", "College 1 - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "College 2", "College 2 - Copy"),
    #"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"College 1", "Dept 1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"College-Dept-1"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"College 1 - Copy", "Dept 2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"College-Dept-2"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"College 2", "Dept 3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"College-Dept-3"),
    #"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"College 2 - Copy", "Dept 4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"College-Dept-4"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns3", {"PIDM"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Value.1", "College"}, {"Value.2", "Dept"}})
in
    #"Renamed Columns"

d chek eDNA 2.0.pbix (18.9 KB)

7

Thanks for answering so quickly. I need to give it a test, as I’m not really following. I need to take off for the rest of the day but will be back at it tomorrow.

So even though we have 3 columns (college 1, Dept 1, Dept 2 (then the same for college 2) when we split we are only splitting into 2 columns?

8

Correct, I recommend clicking through the steps in Power Query to see the transformations.

We’re going from this:
image

Duplicating each college column so we can pair it with Dept2:
image

Merging each College with it’s Dept with a seperator, creating 4 pairs of each:
College1;Dept1
College1;Dept2
College2;Dept1
College2;Dept2
image

We pivot so we get our 4 columns into 4 rows:
image

Finally we split on our delimiter to get them into 2 seperate columns:
image

9

@jamie.bryan I was able to test everything and walked thru the code. Thanks for attaching the code, that was a big help. It is all working as expected. Thanks so much for the help, you have no idea how appreciative I am. Have a great weekend.

Dan

1 Like
10

@d.chek adjunto una forma de hacer el mismo procedimiento un tanto diferente. Ojalá te funcione, por lo menos para este caso

PIDM College 1 Dept 1 College 2 Dept 2
1 VP ART AS PSY 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoLABKOQSEgMhhIBARHKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PIDM = _t, #"College 1" = _t, #"Dept 1" = _t, #"College 2" = _t, #"Dept 2" = _t]),
    Columns = List.Distinct(List.Transform(Table.ColumnNames(Source), each Text.Select(_, {"a".."z", "A".."Z"}))),
    Tabla = Table.FromColumns({Source[PIDM]}&Table.ToColumns(Table.FromRows(List.Split(List.Transform(List.Skip(Table.ToColumns(Source)), each _{0}),2))), Columns),
    Sol = Table.FillDown(Tabla, {"PIDM"})
in
    Sol

Y para este caso:

PIDM	College 1	Dept 1 Dept 2  College 2	Dept 3	Dept 4
1	          VP	ART  	ATY	     AS	        ENG	    HIS

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoLABKOQSEgMiQSRAYDCVc/dyDp4RmsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PIDM = _t, #"College 1" = _t, #"Dept 1" = _t, #"Dept 2" = _t, #"College 2" = _t, #"Dept 3" = _t, #"Dept 4" = _t]),
    Unpivot = Table.UnpivotOtherColumns(Source, {"PIDM"}, "Attribute", "Value"),
    NoNumb = Table.TransformColumns(Unpivot, { "Attribute", each Text.Remove(_, {" ", "0".."9"})}),
    Index = Table.AddIndexColumn(NoNumb, "Index", 1, 1, Int64.Type),
    Pivoted = Table.SelectRows(Table.FillDown(Table.Pivot(Index, List.Distinct(Index[Attribute]), "Attribute", "Value"), {"College"}), each [Dept] <> null),
    Sol = Table.RemoveColumns(Pivoted,{"Index"})
in
    Sol
11

This is my solution with just using UI:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoLABKOQSEgMiQSRAYDCVc/dyDp4RmsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PIDM = _t, #"College 1" = _t, #"Dept 1" = _t, #"Dept 2" = _t, #"College 2" = _t, #"Dept 3" = _t, #"Dept 4" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"PIDM"}, "Attribute", "Dept"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "College", each if Text.Contains([Attribute],"College") then [Dept] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"College"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Attribute], "College")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"PIDM", "College", "Dept"})
in
    #"Removed Other Columns"