提问人：Jon 提问时间：10/25/2023 更新时间：10/28/2023 访问量：62
DAX - 拆分同一字符串中的不同分隔符
DAX - Splitting on different delimiters in the same string
问：
我在 PowerBI 中的单元格数据如下所示：
25.10.2023_1181119 (0.0 St) [] 25.10.2023_1181118 (0.0 St)
我想返回：
| 排 | 货号 |
|---|---|
| 1 | 1181119 |
| 2 | 1181118 |
但是我无法弄清楚如何将起始分隔符设置为“_”，将结束分隔符设置为“ ”或“（”，然后通过字符串重复它。我一直在尝试在 PowerQuery 中使用自定义列，但喜悦为零。
帮助？有人有什么提示吗？
答：
0赞
Sam Nseir
10/25/2023
#1
不能有不同的开始/结束分隔符。
将此内容粘贴到新的空白查询中，以获取此方法：
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVMzTQMzIwMo43NLQwNDS0VNAw0DNQCC7RVIiOVcCQtoBLK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Drop", "Keep"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Drop"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Keep", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Keep.1", "Keep.2"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Keep.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"1", "Drop"}),
#"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Drop"}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns1", "Keep.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"2", "Drop"}),
#"Removed Columns2" = Table.RemoveColumns(#"Split Column by Delimiter3",{"Drop"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns2", {}, "Row", "Article Number")
in
#"Unpivoted Columns"
1赞
horseyride
10/25/2023
#2
这是一个 M 答案，而不是 DAX
您可以将空格拆分为行，然后在 _ 上再次拆分
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Date", "Article Number"}),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter1", each ([Article Number] <> null))
in #"Filtered Rows"
添加列 ..索引列...之后，如果重要，或右键单击删除日期列
0赞
Ron Rosenfeld
10/28/2023
#3
这是另一个Power Query方法，其中的初始列。算法的解释在代码注释中。
您可能希望在末尾设置“商品编号”列的数据类型transform
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVMzTQMzIwMo43NLQwNDS0VNAw0DNQCC7RVIiOVcCQtoBLK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Extract Article" = Table.TransformColumns(#"Changed Type", {
{"Column1", each
let
//split by underscore and space
split = Splitter.SplitTextByAnyDelimiter({"_"," "})(_),
//try to convert each item to a date, and return error status
dates = List.Transform(split,(li)=>(try Date.From(li,"en-150"))[HasError]),
//date positions in the list will be where [HasError] = false
datePositions = List.PositionOf(dates,false, Occurrence.All)
in
//Article number will be in the position just after the date position
List.Accumulate(
datePositions,
{},(s,c)=> s & {split{c+1}}
)
}
}),
//Expand the list column
#"Expanded Column1" = Table.ExpandListColumn(#"Extract Article", "Column1"),
//Add index column for your "Row" column
#"Added Index" = Table.AddIndexColumn(#"Expanded Column1", "Row", 1, 1, Int64.Type),
//Reorder and Rename the columns
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Row", "Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column1", "Article Number"}})
in
#"Renamed Columns"
源
结果
评论