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

Power BI-自定义列获取级别

发布于 2025-02-07 18:31:26 字数 337 浏览 2 评论 0原文

我已经创建了这样的成本帐户

，这就是数据导入数据时的外观。

我不确定如何在Power BI中开发自定义列，该列可以返回水平并同时保持关系。

例如，

对于网站作品，我在自定义列中获得一个值“级别0”，
用于子结构我在自定义列中获得一个值“ 1级” [child to site works parent]
在未分类的土壤中发掘我在未分类的土壤中获得了一个值“自定义列[孩子到子结构父母]

列指示孩子属于父母。如果是762，那么它是762“ 1级”的“ 1级”，

请有人可以帮助使用此用例吗？

非常感谢。

原文

分享到QQ

分享到微博

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

发布评论

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

你不是我要的菜∠ 2025-02-14 18:31:26

由于您正在Power BI工作，因此您可能需要尝试使用DAX的方法。

此方法可能有两个原因更好：

这要简单得多。
它可以自动适应未来的级别变化，例如您添加NBA级别的更改。（使用我较早的基于M的方法，如果您添加了另一个级别，则必须编辑代码 - 当然不是最佳的。）

因此，请尝试使用：

将原始表加载到Power BI中。然后切换到数据视图以查看它。

然后单击列工具＆gt; 新列并将其输入到公式框中：

Hierarchy = PATH(Sheet1[acct_id],Sheet1[parent_acct_id])

然后再次单击 再次输入该公式框中：

Level = "Level " & CONVERT(LEN(Sheet1[Hierarchy])-LEN(SUBSTITUTE(Sheet1[Hierarchy],"|","")),STRING)

然后点击Enter键，或者基本上单击公式外的任何地方盒子。

那应该做到。您应该看到这样的东西：

您可能会发现这个有用的。我做到了。

Since you are working in Power BI, you might want to try this approach that uses DAX.

This method could probably be better for two reasons:

It is much more straightforward.
It could automatically accommodate future level changes like the one where you added the NBA level. (With my earlier M-based approach, you'd have to edit the code if you added another level--that is certainly not optimal.)

So, try this instead:

Load your original table into Power BI. Then switch to the Data view to look at it.

Then click Column Tools > New Column and enter this into the formula box:

Hierarchy = PATH(Sheet1[acct_id],Sheet1[parent_acct_id])

Then click New Column again and enter this into the formula box:

Level = "Level " & CONVERT(LEN(Sheet1[Hierarchy])-LEN(SUBSTITUTE(Sheet1[Hierarchy],"|","")),STRING)

Then either hit the Enter key or click basically anywhere outside the formula box.

That should do it. You should see something like this:

You might find this helpful. I did.

回复收藏 0 原文

故笙诉离歌 2025-02-14 18:31:26

根据您最近添加了NBA的评论，这将更新。
它需要与我的原始答案不同，也许更好的方法。

我从电源查询中的表格开始：

我称其为“表”，并引用了以下内容：

let
    Source = Table,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try if [parent_acct_id] = "" then [acct_short_name] else Source[acct_short_name]{List.PositionOf(Source[acct_id],[parent_acct_id])} & "." & [acct_short_name] otherwise [acct_short_name]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each  try #"Added Custom"[Custom]{List.PositionOf(#"Added Custom"[acct_id],[parent_acct_id])} & "." & [acct_short_name] otherwise [Custom] ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each try #"Added Custom1"[Custom.1]{List.PositionOf(#"Added Custom1"[acct_id],[parent_acct_id])} & "." & [acct_short_name] otherwise [Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each "Level " & Text.From(List.Count(List.FindText(Text.ToList([Custom.2]),".")))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"acct_name", "Custom.2", "Custom.3"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Custom.2", Order.Ascending}})
in
    #"Sorted Rows"

它给了我这样的结果：

当您在帖子中共享电源查询时，仍然可以使用表格查询，您只需要更改我对Table1的引用即可使用我的代码。

This is updated per your recent comment that you added NBA.
It takes a different, perhaps better(?) approach than my original answer.

I started with a table like yours in Power Query:

I called it "Table" and referenced it for the following:

let
    Source = Table,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try if [parent_acct_id] = "" then [acct_short_name] else Source[acct_short_name]{List.PositionOf(Source[acct_id],[parent_acct_id])} & "." & [acct_short_name] otherwise [acct_short_name]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each  try #"Added Custom"[Custom]{List.PositionOf(#"Added Custom"[acct_id],[parent_acct_id])} & "." & [acct_short_name] otherwise [Custom] ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each try #"Added Custom1"[Custom.1]{List.PositionOf(#"Added Custom1"[acct_id],[parent_acct_id])} & "." & [acct_short_name] otherwise [Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each "Level " & Text.From(List.Count(List.FindText(Text.ToList([Custom.2]),".")))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"acct_name", "Custom.2", "Custom.3"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Custom.2", Order.Ascending}})
in
    #"Sorted Rows"

It gives me a result like this:

Assuming you still have your table in Power Query as you shared it in you post, you should only need to change my reference to Table1 in order to use my code.

回复收藏 0 原文

ぺ禁宫浮华殁 2025-02-14 18:31:26

感谢您帮助我离开Marc Pincince。我使用了您提供的相同代码，并且结果略有不同
snap

let
    Source = ALL_XER,
    #"Filtered Rows" = Table.SelectRows(Source, each ([TABLE REF] = "ACCOUNT") and ([Column1] <> "%T")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([#"%F"] = "%R")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"acct_descr", "", "_1", "_2", "_3", "_4", "_5", "_6", "_7", "_8", "_9", "_10", "_11", "_12", "_13", "_15", "_16", "_17", "_18", "_19", "_20", "_21", "_22", "_23", "_24", "_25", "_26", "_27", "_28", "_29", "_30", "_31", "_32", "_33", "_34", "_35", "_36", "_37", "_38", "_39", "_40", "_41", "_42", "_43", "_44", "_45", "_46", "_47", "_48", "_49", "_50", "_51", "_52", "_53", "_54", "_55", "_14"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"acct_id"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each try if [parent_acct_id] = "" then [acct_short_name] else Source[acct_short_name]{List.PositionOf(Source[acct_id],[parent_acct_id])}&"."&[acct_short_name] otherwise [acct_short_name]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each  try #"Added Custom"[Custom]{List.PositionOf(#"Added Custom"[acct_id],[parent_acct_id])} & "." & [acct_short_name] otherwise [Custom] ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each try #"Added Custom1"[Custom.1]{List.PositionOf(#"Added Custom1"[acct_id],[parent_acct_id])} & "." & [acct_short_name] otherwise [Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each "Level " & Text.From(List.Count(List.FindText(Text.ToList([Custom.2]),".")))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"acct_name", "Custom.2", "Custom.3"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Custom.2", Order.Ascending}})
in
    #"Sorted Rows"

Thanks for helping me out Marc Pincince. I used the same code you provided and I'm getting a slightly different result as below
snap

let
    Source = ALL_XER,
    #"Filtered Rows" = Table.SelectRows(Source, each ([TABLE REF] = "ACCOUNT") and ([Column1] <> "%T")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([#"%F"] = "%R")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"acct_descr", "", "_1", "_2", "_3", "_4", "_5", "_6", "_7", "_8", "_9", "_10", "_11", "_12", "_13", "_15", "_16", "_17", "_18", "_19", "_20", "_21", "_22", "_23", "_24", "_25", "_26", "_27", "_28", "_29", "_30", "_31", "_32", "_33", "_34", "_35", "_36", "_37", "_38", "_39", "_40", "_41", "_42", "_43", "_44", "_45", "_46", "_47", "_48", "_49", "_50", "_51", "_52", "_53", "_54", "_55", "_14"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"acct_id"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each try if [parent_acct_id] = "" then [acct_short_name] else Source[acct_short_name]{List.PositionOf(Source[acct_id],[parent_acct_id])}&"."&[acct_short_name] otherwise [acct_short_name]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each  try #"Added Custom"[Custom]{List.PositionOf(#"Added Custom"[acct_id],[parent_acct_id])} & "." & [acct_short_name] otherwise [Custom] ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each try #"Added Custom1"[Custom.1]{List.PositionOf(#"Added Custom1"[acct_id],[parent_acct_id])} & "." & [acct_short_name] otherwise [Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each "Level " & Text.From(List.Count(List.FindText(Text.ToList([Custom.2]),".")))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"acct_name", "Custom.2", "Custom.3"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Custom.2", Order.Ascending}})
in
    #"Sorted Rows"

回复收藏 0 原文

~没有更多了~