Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
If it's advanced M code then your best bet is probably @ImkeF!
Yes we can, I call it "Invoking @ImkeF" 🙂
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
64 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |