|
从备注列提取project code后再和表2做合并即可。
- let
- Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3ZIxDsIwDEXvkrkN/nbSxCtdulQgdax6OAbuxjEICIEEaWgRC2yW/suPv+1xNExMNaGmaCrjfIjBNkgltOuHPZjUTNUTRm90cY0GKz6VTF3fgnzeZF5VkFqvdwax5DCnXppsHWFzOhyHHZJpDagPrzTAquJWvREiWffLH9D8Oc1ES2lOtMjSrRbp2425X1KzCVmX7nGWRgCcvZ75pYFtsb28ygIfrbpHBi4mzKieFI2N+NoIpzM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [日期 = _t, #"不含税金额(本位币)" = _t, 备注 = _t]),
- #"Extracted Text Before Delimiter" = Table.TransformColumns(Table1, {{"备注", each Text.BeforeDelimiter(_, "/"), type text}}),
- Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrT08A0OMDQysFSK1YlWMjLw8HU2NDBF4hhagDkghc4mBobIykyQOUZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Code" = _t]),
- Merge = Table.NestedJoin( Table2, "Project Code", Table1, "备注", "merge", JoinKind.LeftOuter),
- #"Expanded merge" = Table.ExpandTableColumn(Merge, "merge", {"日期", "不含税金额(本位币)"}, {"日期", "不含税金额(本位币)"})
-
- in
- #"Expanded merge"
复制代码
|
|