Forum Discussion
Equivalent result required in Power BI to what VB script achieves in Excel
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?
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.
1 Reply
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.