在 Power-Query 中枚举文本值 [英] Enumerate Text Values in Power-Query

问题描述

我的表中有一个列有一些文本值 (input)，我想为每个唯一的文本值将其转换为数字 (output)，所以我可以做一些回归分析:

I have a column in my table that has some text values (input) which I would like to convert to numbers (output) for each unique text value, so that I can do some regression analysis:

<头>

输入	输出
AOP	1
AOS	2
AOS	2
AOS	2
AOP	1
空	0 或 null
AOP	1

我最初尝试通过几个 Transform: Replace Values 步骤来做到这一点，但我不知道如何:

I initially tried to do this do this with several Transform: Replace Values steps, however I don't know how to:

• 使其灵活地处理不同数量的唯一值(不是硬编码 3 个替换，而是处理 n 其中 n 是 input)
• 对我表的许多列重复此操作
• 尽可能避免循环

什么是更好的方法?

推荐答案

一种方法是使用以下公式添加自定义列，并为您关心的每一列应用它，使用每个文本字符的值生成一个唯一号码

One way is add custom column with below formula, and do that for each column you care to apply it to, using the value of each text character to generate a unique number

= try 
 List.Accumulate(Text.ToList([Input]), "", (state, current)=>
 state&Number.ToText(Character.ToNumber(current), "0000")) otherwise null

这会将所有列的文本转换为唯一的数字，替换原始数据:

this would transform all column's text into unique numbers, replacing the original data:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Function = (x) => try  List.Accumulate(Text.ToList(x), "", (state, current)=> state&Number.ToText(Character.ToNumber(current), "0000")) otherwise null,
TransformList = List.Transform(Table.ColumnNames(Source), each {_ , Function}),
Output = Table.TransformColumns(Source, TransformList)
in Output

这会将所有列的文本转换为唯一的数字，将新列附加到现有列:

this would transform all column's text into unique numbers, appending the new columns to existing columns:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Function = (x) => try List.Accumulate(Text.ToList(x), "", (state, current)=> state&Number.ToText(Character.ToNumber(current), "0000")) otherwise null,
TransformList = List.Transform(Table.ColumnNames(Source), each {_ , Function}),
Output = Table.TransformColumns(Source, TransformList),
Numericals=Table.RenameColumns( Output, List.Zip( { Table.ColumnNames( Output), List.Transform(Table.ColumnNames(Output), each _ &"number") } ) ),
#"Merged Queries" = Table.NestedJoin(Table.AddIndexColumn(Source, "Index", 0, 1),{"Index"},Table.AddIndexColumn(Numericals, "Index2", 0, 1),{"Index2"},"Tabl2",JoinKind.LeftOuter),
#"Expanded Tabl2" = Table.ExpandTableColumn(#"Merged Queries", "Tabl2", Table.ColumnNames( Numericals),Table.ColumnNames( Numericals)),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Tabl2",{"Index"})
in #"Removed Columns"

这篇关于在 Power-Query 中枚举文本值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！