cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
alocasia
Frequent Visitor

Extracting multiple values from one cell into new table and create filtering base on that

Hi,

 

I have Orders data table whith two types of shippment and multiple customers in one cell:

 

Order IDLocal ShippingInternational Shipping
xxx_1AAA, CCC, DDDAAA, BBB
xxx_2BBB, EEEBBB

 

What we want to achieve is to split clients to another table based on Order ID and type of Shippment like in the example bellow:

Order IDClient nameShippment
xxx_1AAALocal
xxx_1CCCLocal
xxx_1DDDLocal
xxx_1AAAInternational
xxx_1BBBInternational
xxx_2BBBLocal
xxx_2EEELocal
xxx_2BBBInternational

 

What we want to achieve is filter data by customers and type of shippment, e.g.

BBB with international shipment

- xxx_1

- xxx_2

BBB with local shipment:

- xxx_1

 

Is this possible to implement in PowerBI?

I'm quite new to PowerBI, so any advices will be helpful.

 

Thanks in advance!

 

 

2 ACCEPTED SOLUTIONS
Arul
Super User
Super User

@alocasia ,

We can do it in Power Query Editor,

1. Split this local shipping column using split by delimiter option.

Arul_0-1678450381717.png

Arul_1-1678450411829.png

 

2.Follow the same steps for International shipping column as well. You would get this result.

Arul_2-1678450471905.png

3. Select local shipping and international shipping then do unpivot columns.

Arul_3-1678450588683.png

4. Then select all the columns and remove duplicates,

Arul_4-1678450673776.png

Thanks,

Arul 





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

Proud to be a Super User!




View solution in original post

KeyurPatel14
Resolver IV
Resolver IV

Hi @alocasia ,
Try the below M Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS1S8HRR0lHyyU9OzFEIzsgsKMjMSwcKeOaVpBblJZZk5uchS8TqRCtVVFTEGwKVODo66ig4OzvrKLi4uMD4Tk5OcEVGQEEgX0fB1dUVwlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" " = _t, #"(blank)" = _t, #"(blank).1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{" ", type text}, {"(blank)", type text}, {"(blank).1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Order ID", type text}, {"Local Shipping", type text}, {"International Shipping", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Local Shipping", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Local Shipping"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Local Shipping", type text}}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"International Shipping", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "International Shipping"),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"International Shipping", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type3", {"Order ID"}, "Attribute", "Value"),
#"Removed Duplicates" = Table.Distinct(#"Unpivoted Columns")
in
#"Removed Duplicates"

 

 

If this helps you then give it a kudos and accept this as a solution.

View solution in original post

5 REPLIES 5
KeyurPatel14
Resolver IV
Resolver IV

Hi @alocasia ,
Try the below M Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS1S8HRR0lHyyU9OzFEIzsgsKMjMSwcKeOaVpBblJZZk5uchS8TqRCtVVFTEGwKVODo66ig4OzvrKLi4uMD4Tk5OcEVGQEEgX0fB1dUVwlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" " = _t, #"(blank)" = _t, #"(blank).1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{" ", type text}, {"(blank)", type text}, {"(blank).1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Order ID", type text}, {"Local Shipping", type text}, {"International Shipping", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Local Shipping", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Local Shipping"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Local Shipping", type text}}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"International Shipping", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "International Shipping"),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"International Shipping", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type3", {"Order ID"}, "Attribute", "Value"),
#"Removed Duplicates" = Table.Distinct(#"Unpivoted Columns")
in
#"Removed Duplicates"

 

 

If this helps you then give it a kudos and accept this as a solution.

Arul
Super User
Super User

@alocasia ,

We can do it in Power Query Editor,

1. Split this local shipping column using split by delimiter option.

Arul_0-1678450381717.png

Arul_1-1678450411829.png

 

2.Follow the same steps for International shipping column as well. You would get this result.

Arul_2-1678450471905.png

3. Select local shipping and international shipping then do unpivot columns.

Arul_3-1678450588683.png

4. Then select all the columns and remove duplicates,

Arul_4-1678450673776.png

Thanks,

Arul 





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

Proud to be a Super User!




alocasia
Frequent Visitor

Thanks! That's really helpful. Is there any option to Split customers in one value in filter so it doesn't show duplicates?

alocasia_0-1678453015733.png

 

@alocasia ,

I think there is some additional spaces added, to remove that do the follow,

1. In power query editor, select the customer column and go to transform tab .

2. Go to format and do trim and clean.

Arul_0-1678453524929.png

Thanks,

Arul

 





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

Proud to be a Super User!




alocasia
Frequent Visitor

Thank you once again! Now it works properly 🙂

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors