Товарищи, большая просьба подсказать, что я делаю не так или предложите другое решение.
Задача: Есть выгрузка чеков (более 1кк), в каждом чеке может быть от 1 до 100 позиций. (Поэтому ищу решение на Power BI) Необходимо составить сводную, часто встречающихся двух или три значений.
Пример результата:
P.S. Я пытался реализовать пример с форума BI, но что-то не получается (Не могу реализовать описанный пример, выдает много ошибок: Expression.Error: A cyclic reference was encountered during evaluation.) Подскажите как повторить. Решение описано по ссылке.
Sub Main()
Dim a As Variant
Dim y As Long
Dim sh As Worksheet
Set sh = ActiveSheet
With sh
a = .Range(.Cells(1, 1), .Cells(.Rows.Count, 5).End(xlUp))
End With
Dim dOrd As Object
Set dOrd = CreateObject("Scripting.Dictionary")
For y = 2 To UBound(a, 1)
If Not dOrd.Exists(CStr(a(y, 1))) Then
Set dOrd.Item(CStr(a(y, 1))) = CreateObject("Scripting.Dictionary")
End If
dOrd.Item(CStr(a(y, 1))).Item(a(y, 3)) = 0
Next
Erase a
Dim dPar As Object
Set dPar = CreateObject("Scripting.Dictionary")
Dim x As Long
Dim s As String
Dim v As Variant
For Each v In dOrd.Items
For y = 0 To v.Count - 2
For x = y + 1 To v.Count - 1
s = Join(Array(v.Keys()(y), v.Keys()(x)), vbTab)
dPar.Item(s) = dPar.Item(s) + 1
Next
Next
Next
If dPar.Count > 0 Then
ReDim a(1 To dPar.Count, 1 To 2)
For y = 1 To dPar.Count
v = Split(dPar.Keys()(y - 1), vbTab)
a(y, 1) = v(0)
a(y, 2) = v(1)
Next
sh.Cells(5, 7).Resize(UBound(a, 1), UBound(a, 2)) = a
End If
End Sub
Андрей VG написал: Aleksxs , а почему вопрос "Курилке"
Думfл, что не стоит засорять основную ветку с Power BI
Код
let
Source = Excel.Workbook(File.Contents("\Desktop\Ex. BI.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"OrderID", Int64.Type}, {"ItemNumber", type number}, {"Item Description", type text}, {"Item Category", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}}),
typed = Table.TransformColumnTypes(#"Changed Type",{{"Item Description", type text}}),
left = Table.RenameColumns(typed, {{"OrderID", "loid"}, {"Item Description", "lid"}}),
right = Table.RenameColumns(typed, {{"OrderID", "roid"}, {"Item Description", "rid"}}),
join = Table.Join(left, {"loid"}, right, {"roid"}),
filter = Table.SelectRows(join, each [lid] < [rid]),
result = Table.Group(filter, {"lid", "rid"}, {{"count", each Table.RowCount(_)}})
in
result
Выдает ошибку: = Table.Join(left, {"loid"}, right, {"roid"}) Expression.Error: A join operation cannot result in a table with duplicate column names ("ItemNumber").Details: [Type]
Оно и понятно, Андрей в своем коде джойнил 2 таблицы, в каждой по 2 столбца, перед этим он назавния всех столбцов переименовывал, чтобы в итоге не было одинаковых названий. А вы в вашем коде джойните таблицы с количеством столбцов большим двух, но переименовываете только по 2 столбца. Вот оно и ругается. В примере необходимо показывать полную структуру источников, и желаемого результата, если не хотите потом проблем с адаптацией готового кода.
let
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[OrderID], [Item Description]],
typed = Table.TransformColumnTypes(Source,{{"Item Description", type text}}),
left = Table.RenameColumns(source, {{"OrderID", "loid"}, {"Item Description", "lid"}}),
right = Table.RenameColumns(source, {{"OrderID", "roid"}, {"Item Description", "rid"}}),
join = Table.Join(left, {"loid"}, right, {"roid"}),
filter = Table.SelectRows(join, each [lid] < [rid]),
result = Table.Group(filter, {"lid", "rid"}, {{"count", each Table.RowCount(_)}})
in
result
Столкнулся с другой проблемой, не могу "запустить" его на PoweBI. У меня Абсолютно нет идей как адаптировать запрос из папки. В папке,(несколько однотипных xlsx, с 1кк+ строк.
Код
let
Source = Folder.Files("С:\****\2020"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"CodeArticle", type text}, {"TicketNumber", type text}, {"Tickets, pcs", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Tickets, pcs", "Source.Name"})
.......
P.S. в Excel через Power Query нет смысла запускать, т.к. выдает ошибку (лимит в 1100к строк), по факту данных более 5кк
Aleksxs написал: в Excel через Power Query нет смысла запускать, т.к. выдает ошибку (лимит в 1100к строк)
И в чем проблема выгрузить результат в модель данных, а не на лист? Там ограничений на количество строк нет. Дальше строите сводную на базе модели данных и анализируете все что нужно.
PooHkrd написал: И в чем проблема выгрузить результат в модель данных, а не на лист
В этом проблем нет, проблема в том, что не знаю как написать ссылку на модель. Поэтому прошу помощи у форумчан. Я всегда делал только внутри Эсель, или SharePoint.