Power Query - Logical calculation from Text

[ngct1112]

Hi, I am trying to transform texts into number with caculations.

At this moment I need to use dozens of steps to process this caculations(split by ",", then conditional columns, then sum together). May I know is there any way it can be simplified in PowerQuery - PowerBi Desktop. Appreciated.

Assuming

A=4

B=1

C=1

(Front/Back)

Text column	Result
Front A	4/0
Back A, Back C	0/5
Front B, Back C	1/1
Front A, Back A	4/4
Front A, Front B, Front C, Back B	6/1
Front A, Front A, Front B, Back B, Back C	9/2

[ImkeF · Accepted solution]

Hi @ngct1112 ,

I would suggest to merge and group.

Transform your numbers in to a suitable table (called "Table":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJRitWJVnICsgzBLGcIKxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Character = _t, Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Character", type text}, {"Number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FB", each {"Front", "Back"}),
    #"Expanded FB" = Table.ExpandListColumn(#"Added Custom", "FB"),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded FB", "Text", each Text.Combine({[FB], [Character]}, " "), type text)
in
    #"Inserted Merged Column"

And then work out the calculations like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkmtKFFIzs8pzc1T0lEKSi0uzSlRitWJVnIrys8rUXAECproG4BFnBKTsxUcdRTAtDNQwkDfFEmpE5KMob4hsiFQGYhhJqgycM0QhjNUrRNQrRm6KZiaIGqRbLbUN1KKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Text column", type text}, {"Result", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Split([Text column], ", ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Custom"}, Table, {"Text"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Number", "FB"}, {"Number", "FB"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table", {"Text column", "FB"}, {{"Sum", each List.Sum([Number]), type nullable number}}, GroupKind.Local),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[FB]), "FB", "Sum", List.Sum)
in
    #"Pivoted Column"

This is fairly dynamic, so you can add calculation items as you like.

[amitchandak]

@ImkeF , Can you help on this.

[ImkeF · Accepted solution]

Hi @ngct1112 ,

I would suggest to merge and group.

Transform your numbers in to a suitable table (called "Table":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJRitWJVnICsgzBLGcIKxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Character = _t, Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Character", type text}, {"Number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FB", each {"Front", "Back"}),
    #"Expanded FB" = Table.ExpandListColumn(#"Added Custom", "FB"),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded FB", "Text", each Text.Combine({[FB], [Character]}, " "), type text)
in
    #"Inserted Merged Column"

And then work out the calculations like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkmtKFFIzs8pzc1T0lEKSi0uzSlRitWJVnIrys8rUXAECproG4BFnBKTsxUcdRTAtDNQwkDfFEmpE5KMob4hsiFQGYhhJqgycM0QhjNUrRNQrRm6KZiaIGqRbLbUN1KKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Text column", type text}, {"Result", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Split([Text column], ", ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Custom"}, Table, {"Text"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Number", "FB"}, {"Number", "FB"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table", {"Text column", "FB"}, {{"Sum", each List.Sum([Number]), type nullable number}}, GroupKind.Local),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[FB]), "FB", "Sum", List.Sum)
in
    #"Pivoted Column"

This is fairly dynamic, so you can add calculation items as you like.

[ngct1112]

@ImkeF I have applied this to my model and it working perfectly. Great thanks for your help!

[ImkeF · Accepted solution]

Hi @ngct1112 ,

great to hear. 

Please mark my post as answer then 😉