excel - 条件列和分隔字段

Question

我每天都会通过 Power Query 将数据加载到我的 Excel 工作表中。我无法控制的数据表是:
tblExport


姓名
公司
状态


简·多伊
美国广播公司
AK，AL，GA，WA

约翰·史密斯
ACME
阿拉斯加州、乔治亚州、佛罗里达州、华盛顿州


对于这个问题，我需要用技术信息字符串替换这些州缩写，我将使用“州全名”作为替代。所以基本上它会根据另一个表检查 COMPANY 字段，因为每个州的每个公司的“技术字符串”都会不同。
到目前为止一切顺利，至少我是这么认为的。然后我拆分 tblExport.States BY ","的分隔符，然后我得到


姓名
公司
国家.1
状态.2
状态.3
国家.4


简·多伊
美国广播公司
AK
铝
遗传算法
西澳

约翰·史密斯
ACME
AK
遗传算法
佛罗里达州
西澳


现在我们引用包含 Company、State、FullStateNames 的表
tblStateNames


公司
缩写
州名


美国广播公司
铝
阿拉巴马ABC

美国广播公司
AK
阿拉斯加ABC

ACME
AK
阿拉斯加ACME

ACME
遗传算法
乔治亚ACME

美国广播公司
佛罗里达州
佛罗里达ABC

美国广播公司
西澳
华盛顿ABC

ACME
西澳
华盛顿ACME

        ST01 = Table.NestedJoin(#"Changed Type1", {"States.1", "Company"}, 
        tblStateNames, {"Abbr", "Company"}, "tblStateNames", 
        JoinKind.LeftOuter),
        ExpST01 = Table.ExpandTableColumn(ST01, "tblStateNames", {"State 
        Name"}, {"tblStateNames.State Name"}),

在我满足诸如 Company ABC 在 TblExport.States 中有 GA 之类的条件之前效果很好，但他们没有资格获得 GA。因此，当它加入查询 tblStateNames 并且 ABC 与 GA 不匹配时，它会返回一个空值。
所以我的列输出是


姓名
公司
ST01
ST02
ST03
ST04


简·多伊
美国广播公司
阿拉斯加ABC
阿拉巴马ABC
无效的
华盛顿ABC

约翰·史密斯
ACME
阿拉斯加ACME
乔治亚ACME
佛罗里达ACME
华盛顿ACME


关于这一点的几件事。最初的 TblExport 是每日摄入量，人们的范围为他们的州，有些人会为零，其余的可以是 1 到 40 个州。挑战以及为什么这是半问题是因为我在列中不能有任何空白。因此，虽然 ST03 显示为 null，但我宁愿让它将 ST04 填充到 ST03 列中。


姓名
公司
ST01
ST02
ST03
ST04


简·多伊
美国广播公司
阿拉斯加ABC
无效的
无效的
华盛顿ABC

约翰·史密斯
ACME
阿拉斯加ACME
乔治亚ACME
佛罗里达ACME
华盛顿ACME


现在我可以做一个有条件的 IF ST02 不等于 <> null 然后 ST02 否则 ST03。然而，在这个例子中，null 只是从 ST03 移动到 ST02。但是，这只会将下一个向下移动一列，因此双空仍然会导致问题。
在我非常新的 PQ 头中，我认为我需要在进行查询查找之前以某种方式验证原始分隔字段中的状态？
我知道我可能过于复杂了，并且仅出于内部原因掩盖了实际代码，我需要更长的时间来尝试解释。 :)
我感谢任何输入，在回复时，请尽量记住我的经验水平。
经验等级:我是一个功能强大的白痴。
帕特里克

Answer 1

如果我理解，这是一种方法:

读入两张表

将导出表状态缩写拆分为 行

加入 StateName 表

按名称和公司分组

从每个子表中提取州名的分隔列表

展开该列表

请阅读代码注释并探索应用步骤以更好地了解正在发生的事情

let

//Read in the two tables
    Source = Excel.CurrentWorkbook(){[Name="tblStateNames"]}[Content],
    tblStateNames = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),

    Source2 = Excel.CurrentWorkbook(){[Name="tblExport"]}[Content],
    tblExport = Table.TransformColumnTypes(Source2, List.Transform(Table.ColumnNames(Source2), each {_, type text})),

//split the States column by comma into Rows
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(tblExport, {
        {"States", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
            let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "States"),

//join with the States Names
    joined = Table.Join(#"Split Column by Delimiter",{"States","Company"},tblStateNames,{"Abbr","COMPANY"}, JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(joined,{"States", "COMPANY", "Abbr"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Name","Company"}, {
        {"States", each Text.Combine([State Name],";"),type text}}),

//split with no column Count option
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Grouped Rows", "States", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv))
in
    #"Split Column by Delimiter1"