Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I have Orders data table whith two types of shippment and multiple customers in one cell:
Order ID | Local Shipping | International Shipping |
xxx_1 | AAA, CCC, DDD | AAA, BBB |
xxx_2 | BBB, EEE | BBB |
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 ID | Client name | Shippment |
xxx_1 | AAA | Local |
xxx_1 | CCC | Local |
xxx_1 | DDD | Local |
xxx_1 | AAA | International |
xxx_1 | BBB | International |
xxx_2 | BBB | Local |
xxx_2 | EEE | Local |
xxx_2 | BBB | International |
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!
Solved! Go to Solution.
We can do it in Power Query Editor,
1. Split this local shipping column using split by delimiter option.
2.Follow the same steps for International shipping column as well. You would get this result.
3. Select local shipping and international shipping then do unpivot columns.
4. Then select all the columns and remove duplicates,
Thanks,
Arul
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.
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.
We can do it in Power Query Editor,
1. Split this local shipping column using split by delimiter option.
2.Follow the same steps for International shipping column as well. You would get this result.
3. Select local shipping and international shipping then do unpivot columns.
4. Then select all the columns and remove duplicates,
Thanks,
Arul
Thanks! That's really helpful. Is there any option to Split customers in one value in filter so it doesn't show duplicates?
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.
Thanks,
Arul
Thank you once again! Now it works properly 🙂
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
59 | |
56 | |
38 | |
29 |
User | Count |
---|---|
82 | |
62 | |
45 | |
41 | |
40 |