问题

假设我有3个国家：

而且每个国家都有他们著名的城市，例如：
美国 - 纽约，西雅图，迈阿密
英国 - 伦敦，伯明翰
日本 - 东京，大阪

现在我想要做的是根据国家添加一个条件列，并列出每个国家的城市：

我尝试了一些方法，但我认为它是错误的：

let
    //反映实际数据源
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"更改类型" = Table.TransformColumnTypes(Source,{{"Country", type text}}),

    //添加带有城市的列
    #"添加城市" = Table.AddColumn(#"更改类型", "City", 
        each if [Country] = "US" then {"New York","Seattle","Miami"}
        else if [Country] = "UK" then {"London","Birmingham"}
        else {"Tokyo","Osaka"}),

    //展开列表以创建新行
    #"展开城市" = Table.ExpandListColumn(#"添加城市", "City")
in
    #"展开城市"

let

//reflect actual data source

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


//Add columns with city
    #"Add City" = Table.AddColumn(#"Changed Type", "City", 
        each if "Country" = "US" then each {"New York","Seattle","Miami"}
        else if "Country" = "UK" then each {"London","Birmingham"}
        else {"Tokyo","Osaka"}),

//Expand the lists to new rows
    #"Expanded City" = Table.ExpandListColumn(#"Add City", "City")
in
    #"Expanded City"

答案1

得分: 1

这将是您的代码的正确版本，主要区别在于使用[Country]代替"Country"。

let  
    //反映实际数据源
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"更改类型" = Table.TransformColumnTypes(Source,{{"Country", type text}}),

    //添加具有城市的列
    #"添加城市" = Table.AddColumn(#"更改类型", "City", 
        each if [Country] = "US" then {"New York","Seattle","Miami"}
        else if [Country] = "UK" then  {"London","Birmingham"}
        else {"Tokyo","Osaka"}),

    //展开列表为新行
    #"展开城市" = Table.ExpandListColumn(#"添加城市", "City")
in  #"展开城市"

尽管如此，这不是最佳方法。创建一个单独的表，使用左外连接在Country字段上执行合并。

然后展开City字段。

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"合并查询" = Table.NestedJoin(Source, {"Country"}, Table2, {"Country"}, "Table2", JoinKind.LeftOuter),
    #"展开Table2" = Table.ExpandTableColumn(#"合并查询", "Table2", {"City"}, {"City"})
in  #"展开Table2"

<details>
<summary>英文:</summary>

This would be the correct version of your code, the main difference being using [Country] in place of &quot;Country&quot;

    let  

    //reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name=&quot;Table1&quot;]}[Content],
    #&quot;Changed Type&quot; = Table.TransformColumnTypes(Source,{{&quot;Country&quot;, type text}}),

    //Add columns with city
    #&quot;Add City&quot; = Table.AddColumn(#&quot;Changed Type&quot;, &quot;City&quot;, 
        each if [Country] = &quot;US&quot; then {&quot;New York&quot;,&quot;Seattle&quot;,&quot;Miami&quot;}
        else if [Country] = &quot;UK&quot; then  {&quot;London&quot;,&quot;Birmingham&quot;}
        else {&quot;Tokyo&quot;,&quot;Osaka&quot;}),

    //Expand the lists to new rows
    #&quot;Expanded City&quot; = Table.ExpandListColumn(#&quot;Add City&quot;, &quot;City&quot;)
    in  #&quot;Expanded City&quot;

That said, it is not the way to go.  Create a separate Table, do a (home .. merge) merge using left outer join on the Country field


[![enter image description here][1]][1]

Then expand the City field

    let  Source = Excel.CurrentWorkbook(){[Name=&quot;Table1&quot;]}[Content],
    #&quot;Merged Queries&quot; = Table.NestedJoin(Source, {&quot;Country&quot;}, Table2, {&quot;Country&quot;}, &quot;Table2&quot;, JoinKind.LeftOuter),
    #&quot;Expanded Table2&quot; = Table.ExpandTableColumn(#&quot;Merged Queries&quot;, &quot;Table2&quot;, {&quot;City&quot;}, {&quot;City&quot;})
    in  #&quot;Expanded Table2&quot;

[![enter image description here][2]][2]


  [1]: https://i.stack.imgur.com/adarh.png
  [2]: https://i.stack.imgur.com/Ena9G.png

</details>



# 答案2
**得分**: 1

// 添加城市列
#"添加城市" = Table.AddColumn(#"更改类型", "城市", each
if [国家] = "美国" then {"纽约","西雅图","迈阿密"}
else if [国家] = "英国" then {"伦敦","伯明翰"}
else {"东京","大阪"}),

<details>
<summary>英文:</summary>

Some syntax issues but close:

//Add columns with city
#"Add City" = Table.AddColumn(#"Changed Type", "City", each
if [Country] = "US" then {"New York","Seattle","Miami"}
else if [Country] = "UK" then {"London","Birmingham"}
else {"Tokyo","Osaka"}),

</details>