Страницы: 1
RSS
Референты, товарные пары Power BI
 
Товарищи, большая просьба подсказать, что я делаю не так или предложите другое решение.

Задача:
Есть выгрузка чеков (более 1кк), в каждом чеке может быть от 1 до 100 позиций. (Поэтому ищу решение на Power BI)
Необходимо составить сводную, часто встречающихся двух или три значений.

Пример результата:


P.S. Я пытался реализовать пример с форума BI, но что-то не получается (Не могу реализовать описанный пример, выдает много ошибок:  Expression.Error: A cyclic reference was encountered during evaluation.) Подскажите как повторить.
Решение описано по ссылке.
 
Доброе время суток
Aleksxs, а почему вопрос "Курилке"?
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[OrderID], [Item Description]],
    typed = Table.TransformColumnTypes(Source,{{"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
 
Код
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

Авось вариант на VBA сгодится.
 
упс. День прколов  :(
Изменено: Андрей VG - 18.02.2020 19:38:36
 
упс, а тему то в клиентоориентированный раздел перенесли
Изменено: Андрей VG - 18.02.2020 19:31:54
 
Цитата
Андрей 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]
Изменено: Aleksxs - 19.02.2020 12:53:35
 
Оно и понятно, Андрей в своем коде джойнил 2 таблицы, в каждой по 2 столбца, перед этим он назавния всех столбцов переименовывал, чтобы в итоге не было одинаковых названий. А вы в вашем коде джойните таблицы с количеством столбцов большим двух, но переименовываете только по 2 столбца. Вот оно и ругается.
В примере необходимо показывать полную структуру источников, и желаемого результата, если не хотите потом проблем с адаптацией готового кода.
Изменено: PooHkrd - 19.02.2020 12:57:53
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
в каждой по 2 столбца
А зачем там больше, если нужно вычислить количество чеков, в которых встречалась данная пара товаров?
 
Цитата
Андрей VG написал:
А зачем там больше
Это не ко мне, это ТС решил вашим кодом джойнить таблицы с кучей столбцов. Я ему просто объяснил что именно у него не получается.
Вот горшок пустой, он предмет простой...
 
Алексей, я это к тому, что в коде не двусмысленно написано, что использовать только два столбца
Цитата
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[OrderID], [Item Description]]
Код же всё-таки требует изучения, а не использования как заклинания. Это уже шаманство какое-то будет :)
 
Спасибо за помощь!!, рабочий для Эксель

Код
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.
 
В панельке с запросами ПКМ по любому из них и жмем сюда:

потом ставим галку сюда и жмем ОК:

Потом при создании сводной таблицы в качестве источника данных выбираем модель данных:

И идем курить мануалы по РР.
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх