时间戳

首页 > 解决方案 > powerbi 根据现有表创建新表

powerbi - powerbi 根据现有表创建新表

问题描述

嗨，我正在尝试根据 power bi 中的现有表创建一个新表，我该怎么做？

现有表

现有表

新表

新表

可以从 table3 导出新的表组列

标签： powerbidaxpowerquery

解决方案

电源查询解决方案

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WSlTSUUpMBhLJeToK+aW2KYlKsTrRSklAkZRMhHByKVg4GYvqWAA=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [user = _t, status = _t, dn = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"user", type text}, {"status", type text}, {"dn", type text}}
  ), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Changed Type", 
    "cn, ou=", 
    "", 
    Replacer.ReplaceText, 
    {"dn"}
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Replaced Value", {"user"}), 
  #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns", {"dn"}), 
  #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"), 
  #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type), 
  #"Merged Queries" = Table.NestedJoin(
    #"Removed Columns", 
    {"dn"}, 
    #"Added Index", 
    {"dn"}, 
    "Added Index", 
    JoinKind.LeftOuter
  ), 
  #"Expanded Added Index" = Table.ExpandTableColumn(
    #"Merged Queries", 
    "Added Index", 
    {"Index"}, 
    {"Index"}
  ), 
  #"Pivoted Column" = Table.Pivot(
    #"Expanded Added Index", 
    List.Distinct(#"Expanded Added Index"[status]), 
    "status", 
    "Index", 
    List.Count
  ), 
  #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Total", each [ac] + [di])
in
  #"Added Custom"

推荐阅读