Блог

Я хотел бы сделать своего рода перенос для следующей входной таблицы на основе столбца B5. На выходе отображаются уникальные значения B5 и переносятся все значения в B3, связанные с тем же значением в B5.

  ---- ---- ------- ---- ---- 
| B1 | B2 |   B3  | B4 | B5 |
 ---- ---- ------- ---- ---- 
|  R |  1 |  1624 |  M | 11 |
 ---- ---- ------- ---- ---- 
|  R |  1 |  4606 |  M | 12 |
 ---- ---- ------- ---- ---- 
|  R |  1 |  4609 |  M | 12 |
 ---- ---- ------- ---- ---- 
|  R |  1 |  4630 |  M | 12 |
 ---- ---- ------- ---- ---- 
|  R |  1 |  4690 |  M | 12 |
 ---- ---- ------- ---- ---- 
|  R |  1 | 25660 |  M | 27 |
 ---- ---- ------- ---- ---- 
|  R |  1 | 73003 |  M | 28 |
 ---- ---- ------- ---- ---- 
|  R |  1 | 73006 |  M | 28 |
 ---- ---- ------- ---- ---- 
|  R |  1 | 73008 |  M | 28 |
 ---- ---- ------- ---- ---- 
|  R |  1 | 73013 |  M | 28 |
 ---- ---- ------- ---- ---- 
  

Ниже мои текущие шаги, но получение не ожидаемого результата.

 let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"B1", type text}, {"B2", Int64.Type}, {"B3", Int64.Type}, {"B4", type text}, {"B5", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"B5", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"B5", type text}}, "en-US")[B5]), "B5", "B1")
in
    #"Pivoted Column"
  

Пожалуйста, помогите в этом. Заранее спасибо.

Вы имеете в виду что-то подобное

 let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"B1", type text}, {"B2", Int64.Type}, {"B3", Int64.Type}, {"B4", type text}, {"B5", Int64.Type}}),
    #"Cleaned Text" = Table.TransformColumns(#"Changed Type",{{"B1", Text.Clean, type text}, {"B4", Text.Clean, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Cleaned Text",{"B1", "B2", "B4"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"B5"}, {{"tblB3", each _, type table [B3=nullable number, B5=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "lstB3", each Table.Column([tblB3],"B3")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"tblB3"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"lstB3", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "lstB3", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"lstB3.1", "lstB3.2", "lstB3.3", "lstB3.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"lstB3.1", Int64.Type}, {"lstB3.2", Int64.Type}, {"lstB3.3", Int64.Type}, {"lstB3.4", Int64.Type}})
in
    #"Changed Type1"
  

Это результат, который я получаю

Приведенный выше M-код имеет тот недостаток, что он не будет генерировать правильное количество столбцов, если вы добавите или удалите что-то к данным.

Если вы посмотрите на набор данных

вывод

но это должно быть

Это связано с тем, что приведенный выше запрос учитывает только количество разделителей при создании запроса.

С помощью следующих двух запросов можно устранить проблему. Следующий запрос подготовит данные (определит их только как соединение)

 let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"B1", type text}, {"B2", Int64.Type}, {"B3", Int64.Type}, {"B4", type text}, {"B5", Int64.Type}}),
    #"Cleaned Text" = Table.TransformColumns(#"Changed Type",{{"B1", Text.Clean, type text}, {"B4", Text.Clean, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Cleaned Text",{"B1", "B2", "B4"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"B5"}, {{"tblB3", each _, type table [B3=nullable number, B5=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "B3", each Table.Column([tblB3],"B3")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"tblB3"}),
    tblToSplit = Table.TransformColumns(#"Removed Columns1", {"B3", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    tblToSplit
  

Второй запрос, основанный на приведенном здесь коде, будет динамически разделять столбец

 let
  Source = tblSplit,
  DynamicColumnList = List.Transform({
    1..List.Max(
      Table.AddColumn(Source, "Custom", each List.Count(
        Text.PositionOfAny([B3], {","}, Occurrence.All)
      ))[Custom]
    )   1
  }, each "B3." amp; Text.From(_)),
  #"Split Column by Delimiter" = Table.SplitColumn(
    Source, 
    "B3", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
    DynamicColumnList
  )
in
  #"Split Column by Delimiter"