Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RitaHL
Frequent Visitor

Split column to rows issue

Hi there, 

 

I thought it would be a simple matter but I have not found a solution despite a few hours spent on this. 

 

I have a table that looks like the one below, just with many more rows and values:

 

Key Value

A and B1
A and B2
C3
D4
E5
X and Y6
X and Y7

 

My desired output is as below:

Key Value

A1
B2
C3
D4
E5
X6
Y7

After multiple tries of split, group, split into rows, adding index and split,... , remove duplicates, I always get the result below. 

Key Value

A1
B1
C3
D4
E5
X6
Y6

Would anyone be able to help me on this question? 

Thanks in advance!

1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

You can try something like the following.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRIzEtRcFLSUTJUitVB5huB+c5AljGY5QJkmYBZrkCWKZgVAVYdCeSbofHNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Value = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Key"}, {{"AllRows", each _[Value], type table [Key=nullable text, Value=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "NewKey", each [Key], type text),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"NewKey", Splitter.SplitTextByDelimiter(" and ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "NewKey"),
    #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Position", each [AllRows]{List.PositionOf(Text.Split([Key], " and "), [NewKey])}, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Key", "AllRows"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewKey", "Key"}, {"Position", "Value"}})
in
    #"Renamed Columns"

From:

jgeddes_0-1752689598192.png

 

To:

jgeddes_1-1752689614207.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
SundarRaj
Super User
Super User

Hi @RitaHL,
Here's an M Code solution for your query. I'll leave the code used below. Thanks. Do ping back in case of any queries or confusion. Thanks!

Code:
let
Source = #table(
{"Key", "Value"},
{{"A and B", 1}, {"A and B", 2}, {"C", 3}, {"D", 4}, {"E", 5}, {"X and Y", 6}, {"X and Y", 7}}
),
List = List.Distinct(List.Combine(List.Transform(Source[Key], each Text.Split(_, "and")))),
Table = Table.TransformColumns(Table.AddIndexColumn(Source, "Keys", 0, 1), {"Keys", each List{_}})[
[Keys],
[Value]
]
in
Table

Sundar Rajagopalan
jgeddes
Super User
Super User

You can try something like the following.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRIzEtRcFLSUTJUitVB5huB+c5AljGY5QJkmYBZrkCWKZgVAVYdCeSbofHNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Value = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Key"}, {{"AllRows", each _[Value], type table [Key=nullable text, Value=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "NewKey", each [Key], type text),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"NewKey", Splitter.SplitTextByDelimiter(" and ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "NewKey"),
    #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Position", each [AllRows]{List.PositionOf(Text.Split([Key], " and "), [NewKey])}, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Key", "AllRows"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewKey", "Key"}, {"Position", "Value"}})
in
    #"Renamed Columns"

From:

jgeddes_0-1752689598192.png

 

To:

jgeddes_1-1752689614207.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Greg_Deckler
Community Champion
Community Champion

@RitaHL Question, why not:

A 1

A 2

B 1

B 2

C 3

D 4

...

 

Because that's pretty easy like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRIzEtRcFLSUTJUitVB5huB+c5AljGY5QJkmYBZrkCWKZgVAVYdCeSbofHNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Value", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Key", Splitter.SplitTextByDelimiter(" and ", QuoteStyle.Csv), {"Key.1", "Key.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Key.1", type text}, {"Key.2", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Value"}, "Attribute", "Value.1"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.