Equivalent result required in Power BI to what VB script achieves in Excel

[Anonymous]

I'm fairly new to Power BI coming from a strong Excel VB coding background.

I need to move off Excel onto Power BI for a variety of corporate reasons, but how can I replicate the results of an Excel 'grouping' VB script I wrote, to an equivalent analysis result within Power BI?

In Excel we have a table with transactions on the Y axis in Col A (1 transaction per row) and for each transaction there may be one or many of the components which are listed on the  x-axis in Row 1, used in each transaction, as determined by a '1' at the intersection of the transaction (row) and each component (column).

My (Excel) VB reads the name of each component used by each transaction and concatenates them as a list in a cell to the right of the last component column, so we can analyse which transactions used which components, and how often components are used together.

I need to somehow deliver something similar using Power BI, be it through using M or some other clever way.

Any suggestions from anyone?

[SpartaBI · Accepted solution]

Hi @Anonymous,

I believe you are looking for something like this:

Have a look at the following M code. This is a simple sketch built, mostly, using the PQ GUI.
It can probably be improved.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcKxOtFKRkgiYAFjGAemwgQhAOabwiRhCswQAmC+OVQxTN4CzleKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TransactionID = _t, ComponentA = _t, ComponentB = _t, ComponentC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TransactionID", Int64.Type}, {"ComponentA", Int64.Type}, {"ComponentB", Int64.Type}, {"ComponentC", Int64.Type}}),
    Buffered = Table.Buffer(#"Changed Type"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Buffered, {"TransactionID"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"TransactionID"}, {{"AttributeList", each _[Attribute], type list}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"AttributeList", each Text.Combine(List.Transform(_, Text.From), " | "), type text}),
    #"Merged Queries" = Table.NestedJoin(Buffered, {"TransactionID"}, #"Extracted Values", {"TransactionID"}, "Attributes", JoinKind.LeftOuter),
    #"Expanded Attributes" = Table.ExpandTableColumn(#"Merged Queries", "Attributes", {"AttributeList"}, {"AttributeList"})
in
    #"Expanded Attributes"

In case it answered your question please mark this as a solution to help other community members find it more easily.