|
放在python里处理起来更有效率:
- let
- Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7PCoJAEMfxd5mzF3sd2ZeJgiQsw4UILCIQPGgo1aGgfJr941u0MztqHb8ffrNsFEEIAehTbmUFInBJ3dwdcSPY9XOCGULRmWwEutmfTfsekUaystuhaXO7TECL9KgSD5hGPtSh9Bly1+mfMtvFjgHvdJyppNH5yxuS2pR61f2eDi/2Rd3LJRtNP1f3MTOPQYgv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [level = _t, name = _t]),
- #"Changed Type" = Table.TransformColumnTypes(Source,{{"level", Int64.Type}, {"name", type text}}),
- #"Run Python script" = Python.Execute("dataset['sup'] = dataset['level'].apply(lambda x: x // 10)#(lf)#(lf)def fx_Upstream(sup, hierarchy):#(lf) try:#(lf) r = dataset[dataset['level']==sup].iloc[0]#(lf) return fx_Upstream(r['sup'], r['name']+'|'+hierarchy)#(lf) except Exception as e:#(lf) return hierarchy#(lf)#(lf)dataset['hierarchy'] = dataset.apply(lambda row: fx_Upstream(row['sup'], row['name']), axis=1)",[dataset=#"Changed Type"]),
- dataset = #"Run Python script"{[Name="dataset"]}[Value],
- #"Split Column by Delimiter" = Table.SplitColumn(dataset, "hierarchy", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"hierarchy.1", "hierarchy.2", "hierarchy.3", "hierarchy.4"})
- in
- #"Split Column by Delimiter"
复制代码
|
|