当前位置：文江博客话题详情

PowerBi 中的拆分列

发布于 2025-01-11 10:46:00 字数 241 浏览 0 评论 0原文

我一直被困在 PowerBi 的拆分列中。下图是我需要拆分的数据的示例。

在资格选项卡中，我希望将这些详细信息与日期一起分隔到单独的列中。

你认为你能帮助我吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

很酷又爱笑 2025-01-18 10:46:00

我一开始没有注意到所有数据都在一行中

所以...

右键单击“资格”列，拆分列...按分隔符...[x] --Custom--，然后进行自定义选择换行（通过单击该框并使用在框底部插入换行的特殊字符），对于高级选项，选择 [x] 行

这可以让你在不同的行上

右键单击资格柱、分割柱..通过分隔符 .. 选择或输入分隔符：选择 --custom-- 并输入空格(

注意：在上面的步骤中，我的意思是实际使用两个字符 - 空格后跟括号

在此处输入图像描述 < /a>

右键单击新列“替换值”，然后将 ) 替换为任何内容

单击选择该列并转换...数据类型..日期

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Qualifications", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Qualifications"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Qualifications", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Qualifications", Splitter.SplitTextByEachDelimiter({" ("}, QuoteStyle.Csv, false), {"Qualifications.1", "Qualifications.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Qualifications.1", type text}, {"Qualifications.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",")","",Replacer.ReplaceText,{"Qualifications.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Qualifications.2", type date}})
in  #"Changed Type2"

I didn't notice at first that all the data is in a single row

So...

Right click the Qualifications column, split column ... by delimiter... [x] --Custom--, and for the custom choose a line feed (by clicking in that box and using insert special character for line feed at the bottom of the box), for advanced options pick [x]rows

That gets you this, on different rows

Right click the qualifications column, split column .. by delimiter .. select or enter delimiter: choose --custom-- and enter space(

Note: In step above I mean to actually use two characters - a space followed by a parenthesis

Right click the new column, Replace Values, and replace ) with nothing

Click select that column and transform ... data type .. date

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Qualifications", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Qualifications"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Qualifications", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Qualifications", Splitter.SplitTextByEachDelimiter({" ("}, QuoteStyle.Csv, false), {"Qualifications.1", "Qualifications.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Qualifications.1", type text}, {"Qualifications.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",")","",Replacer.ReplaceText,{"Qualifications.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Qualifications.2", type date}})
in  #"Changed Type2"

回复收藏 0 原文

墨落画卷 2025-01-18 10:46:00

你没有表现出你想要的结果。特别是，如果您想保留周围的括号，则不会显示。

如果您确实想保留它们，则只需在高级编辑器中要进行拆分的位置插入此 M 代码即可在转换上进行拆分。

let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Qualifications", type text}}),

//split on transition from " " to "("
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Qualifications", 
      Splitter.SplitTextByCharacterTransition((c) => c <> "(", {"("}), {"Qualifications", "Date"}),

//remove trailing " " from first column
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Character Transition",{{"Qualifications", Text.Trim, type text}})
in
    #"Trimmed Text"

如果您想删除括号，则只需拆分 "(" 并删除尾随的 ")"

You don't show what you want for a result. In particular you don't show if you want to retain the surrounding parentheses.

If you do want to retain them, then merely split on the transition by inserting this M-Code in the Advanced Editor in the place where you want to do the split.

let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Qualifications", type text}}),

//split on transition from " " to "("
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Qualifications", 
      Splitter.SplitTextByCharacterTransition((c) => c <> "(", {"("}), {"Qualifications", "Date"}),

//remove trailing " " from first column
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Character Transition",{{"Qualifications", Text.Trim, type text}})
in
    #"Trimmed Text"

If you want to remove the parentheses, then just split on the "(" and remove the trailing ")"

回复收藏 0 原文

~没有更多了~