英文:
PowerQuery expand column containing list
问题
我有一个来自数据源的订单表格。这些订单中的每一个都与一些销售代表相关联。我的数据源有多列表示这些代表。我已经创建了一个自定义列，将这些名称附加到一个列表中。由于我希望可视化和切分数据的方式，我想从现有表格中创建一个新表格。新表格将包含订单号列和列表列，但会根据列表列中的销售代表数量扩展列表列，创建N个基于销售代表数量的行。
如何在Power BI中使用Power Query或DAX执行此操作？
谢谢！
源表格
| 订单号 | 所有代表 | 许多其他列 |
|---|---|---|
| 1234567 | {R1, R2, R3} | 其他内容 |
| 0000012 | {R1, R2} | 其他内容 |
期望的新表格
| 订单号 | 销售代表 |
|---|---|
| 1234567 | R1 |
| 1234567 | R2 |
| 1234567 | R3 |
| 0000012 | R1 |
| 0000012 | R2 |
英文:
I have a table of orders from a data source. Each of those orders has a number of sales reps associated with them. My data source has multiple columns representing those reps. I have created a custom column that appends those names into a list. Because of how I would like to visualize and slice the data, I would like to create a new table from the existing table. The new table would contain the order number column and the list column but would expand the list column to create N number of rows based on how many sales reps were in the list column.
How can I do this using Power Query or DAX in Power BI?
Thank you!
Source Table
| Order # | All Reps | Many other Cols |
|---|---|---|
| 1234567 | {R1, R2, R3} | stuff |
| 0000012 | {R1, R2} | stuff |
Desired new table
| Order # | Sales Rep |
|---|---|
| 1234567 | R1 |
| 1234567 | R2 |
| 1234567 | R3 |
| 0000012 | R1 |
| 0000012 | R2 |
答案1
得分: 1
如果"All Reps"列包含实际列表，则在Power Query中只需使用该列顶部的箭头来展开到新行。
如果其中包含实际文本，然后在转换后进行展开，如下所示：
转换 = Table.TransformColumns(#"PriorStepNameHere",{{"reps", each Expression.Evaluate(_)}})
英文:
If the All Reps column contains actual lists then in powerquery just use arrow atop that column to expand to new rows
if you have actual text in there like
then expand it after converting as follows
Convert = Table.TransformColumns(#"PriorStepNameHere",{{"reps", each Expression.Evaluate(_)}})
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论