Dynamic Calculated Columns - M Query (advanced)

[Anonymous]

Guys,

I have a reconciliation script that I often use; it's essentially a FullOuterJoin between two tables (orange and green below).  Once the data is loaded to Power BI, I then manually add several columns (in grey below) to see if the values match.

Example:

I would like to automate the creation of the columns highlighted in the red box.  Obviously the solution would need to be dynamic, i.e., should TableA or TableB change in column count, then so would the columns in the red box.

Thanks,

Simon

[Greg_Deckler]

If it's advanced M code then your best bet is probably @ImkeF!

[Anonymous]

Oooo, we can tag people? Time to bring in the pros! @mattmasson 

[Greg_Deckler]

Yes we can, I call it "Invoking @ImkeF" 🙂

[ImkeF · Accepted solution]

Oh, thanks Simon... I would have thought that you know me by now:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXIsS80rTQUysvJRuG6lOTlKsTpAdYnZIH5wSVFqagmQAeWDUGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TableA.Name = _t, TableA.Adress = _t, TableB.Name = _t, TableB.Adress = _t, Recon.JoinType = _t]),
    FetchColNames = Table.ColumnNames(Source),
    ConvertToTable = Table.FromList(FetchColNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Duplicate = Table.DuplicateColumn(ConvertToTable, "Column1", "Column1 - Copy"),
    SplitCol = Table.SplitColumn(Duplicate, "Column1 - Copy", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1 - Copy.1", "Column1 - Copy.2"}),
    Group = Table.Group(SplitCol, {"Column1 - Copy.2"}, {{"Count", each Table.RowCount(_), type number}, {"All", each _[Column1], type table}}),
    Rename = Table.RenameColumns(Group,{{"Column1 - Copy.2", "PairName"}}),
    TableOfPairs = Table.Buffer(Table.SelectRows(Rename, each ([Count] = 2))),
    Compare = Table.AddColumn(Source, "Comparison", each List.Transform(TableOfPairs[All], (x) => Record.Field(_, x{0}) = Record.Field(_, x{1}))),
    CreateTable = Table.AddColumn(Compare, "Custom", each #table(TableOfPairs[PairName], {[Comparison]})),
    DynamicExpansion = Table.ExpandTableColumn(CreateTable, "Custom", TableOfPairs[PairName]),
    Cleanup = Table.RemoveColumns(DynamicExpansion,{"Comparison"})
in
    Cleanup

No pivoting, so it should be fast.

[Anonymous]

Thanks Imke!

Super impressive - for each row, creating a list of a list of pairs that are used as variables against the main table.  I've never used Record.Field before so that was nice to see!

Simon