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.
Sorry for the titlegore! Here's the situation:
I want to start with something like this:
TableType | TableColumn |
1 | Table |
1 | Table |
2 | Table |
2 | Table |
2 | Table |
3 | Table |
3 | Table |
And my desired result is this:
TableType | JoinedTableColumn |
1 | Table |
2 | Table |
3 | Table |
...Where the final tables are joined 1+1, 2+2+2, 3+3.
My instinct says iterate through the list, if [TableType]{Counter} = [TableType]{Counter-1} then join Table to previous Table, else Table. The problem is if I use something like List.Generate, I don't have a way of storing previous table from the last run-through?
Again, very new to M. Any thoughts are appreciated, thanks!
Solved! Go to Solution.
@WolfBiber isn't Power Query the part before the datamodel?
Below 2 queries and a video to show how it looks like. Tables are joined and expanded.
Remark: the tables that are joined, must have different column names (except for "PrimaryKey").
Should you have further requirements, please provide a link to a file with representative test data.
Query Start:
let Source = #table(type table[TableType = number,TableColumn = table,joinKind = number], {{1,Table1,null}, {1,Table2,JoinKind.Inner}, {2,Table3,null}, {2,Table4,JoinKind.FullOuter}, {2,Table5,JoinKind.FullOuter}, {3,Table6,null}, {3,Table7,JoinKind.FullOuter}}) in Source
Query Result:
let Source = Table.Buffer(Start), JoinTables = (Table as table) as table => let RemovedTableType = Table.RemoveColumns(Table,{"TableType"}), TableRows = Table.ToRows(RemovedTableType), JoinTables = List.Accumulate(List.Skip(TableRows),TableRows{0}{0},(jt,t) => let Joined = Table.NestedJoin(jt,{"PrimaryKey"},t{0},{"PrimaryKey"},"JoinedTableColumn",t{1}), ColumnNames = List.Difference(Table.ColumnNames(t{0}),{"PrimaryKey"}), Expanded = Table.ExpandTableColumn(Joined,"JoinedTableColumn",ColumnNames) in Expanded) in JoinTables, #"Grouped Rows" = Table.Group(Source, {"TableType"}, {{"AllRows", JoinTables, type table}}) in #"Grouped Rows"
Video:
are you removing duplicate rows? Is that what you want?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k sorry it wasn't clear. I'm trying to join the tables together. For example, there are 3 tables of TableType=2. I'd like to join all 3 of these tables, and that's the merged table that's listed next to TableType=2 in the final table.
You can group on TableType with a dummy operation for TableColumn (e.g. minimum).
After the code is generated, adjust it to combine the tables:
= Table.Group(PreviousStep, {"TableType"}, {{"JoinedTableColumn", each Table.Combine([TableColumn]), type table}})
@MarcelBeug thanks for your help again. This is a very helpful function, and I might get this to do what I need. It would be better if it were a join rather than a combine function, however -- is there any chance I could achieve that? I tried this (below) but because the tables are separated by primary keys in the join function it doesn't want to run as I've written it:
= Table.Group(PreviousStep, {"TableType"}, {{"JoinedTableColumn", each Table.Join([TableColumn], "PrimaryKey"), type table}})
Hey,
are you sure you want a join? Maybe append is the right solution if you want to generate a big table from 2 Source Tables.
Or I misunderstood you 🙂
Greetings,
Wolf
@WolfBiber I know it sounds crazy, but I need to do a join. The purpose is to compare these three tables -- ultimately, I will complicate the situation further (in some cases I will want an inner join, in some cases other types of joins), but append/combine will never be the goal.
Ok, so maybe it would be better to generate a surrogate Key to benefit from a solid Data Model in Powerbi. My eperience is:
Keep the complexity out of the datamodel. Sorry, I'm a traditional BI developer 🙂
But without understanding you goal its hard to help further
Good luck
Greetings,
Wolf
1.) Append tables
2.) have a look to following post
Joining tables should be possible using List.Accumulate.
But it will be more complicated; maybe I can take a further look tomorrow Central European Time (UTC + 1),
@WolfBiber isn't Power Query the part before the datamodel?
Below 2 queries and a video to show how it looks like. Tables are joined and expanded.
Remark: the tables that are joined, must have different column names (except for "PrimaryKey").
Should you have further requirements, please provide a link to a file with representative test data.
Query Start:
let Source = #table(type table[TableType = number,TableColumn = table,joinKind = number], {{1,Table1,null}, {1,Table2,JoinKind.Inner}, {2,Table3,null}, {2,Table4,JoinKind.FullOuter}, {2,Table5,JoinKind.FullOuter}, {3,Table6,null}, {3,Table7,JoinKind.FullOuter}}) in Source
Query Result:
let Source = Table.Buffer(Start), JoinTables = (Table as table) as table => let RemovedTableType = Table.RemoveColumns(Table,{"TableType"}), TableRows = Table.ToRows(RemovedTableType), JoinTables = List.Accumulate(List.Skip(TableRows),TableRows{0}{0},(jt,t) => let Joined = Table.NestedJoin(jt,{"PrimaryKey"},t{0},{"PrimaryKey"},"JoinedTableColumn",t{1}), ColumnNames = List.Difference(Table.ColumnNames(t{0}),{"PrimaryKey"}), Expanded = Table.ExpandTableColumn(Joined,"JoinedTableColumn",ColumnNames) in Expanded) in JoinTables, #"Grouped Rows" = Table.Group(Source, {"TableType"}, {{"AllRows", JoinTables, type table}}) in #"Grouped Rows"
Video:
@MarcelBeug I just noticed that in your example, PrimaryKey is being lost when you perform a FullOuter for TableType=2. Any thoughts on that? I am having trouble attempting to debug it because this is a bit out of my knowledge level of M and I don't totally follow how the JoinTables code works.
Adjusted code to also expand the PrimaryKey of the nested tables.
In order to prevent name conflicts, an index column is added (and changed to text), and the PrimaryKey columns are expanded as PrimaryKey1, PrimaryKey2, etcetera.
Null values in the PrimaryKey field are replaced with the values from PrimaryKeyn, after which the PrimaryKeyn column is removed.
let Source = Table.Buffer(Start), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Index", type text}}), JoinTables = (Table as table) as table => let RemovedTableType = Table.RemoveColumns(Table,{"TableType"}), TableRows = Table.ToRows(RemovedTableType), JoinTables = List.Accumulate(List.Skip(TableRows),TableRows{0}{0},(jt,t) => let Joined = Table.NestedJoin(jt,{"PrimaryKey"},t{0},{"PrimaryKey"},"JoinedTableColumn",t{1}), ColumnNames = List.Difference(Table.ColumnNames(t{0}),{"PrimaryKey"}), Expanded = Table.ExpandTableColumn(Joined,"JoinedTableColumn",{"PrimaryKey"}&ColumnNames,{"PrimaryKey"&t{2}}&ColumnNames), CopyKey = Table.ReplaceValue(Expanded,null,each Record.Field(_,"PrimaryKey"&t{2}),Replacer.ReplaceValue,{"PrimaryKey"}), RemoveKey = Table.RemoveColumns(CopyKey,"PrimaryKey"&t{2}) in RemoveKey) in JoinTables, #"Grouped Rows" = Table.Group(#"Changed Type", {"TableType"}, {{"AllRows", JoinTables, type table}}) in #"Grouped Rows"
@MarcelBeug I think it is the tool to build the datamodel. But my approach is always to build and keep it as simple as possible (coming from MS SQL World SSIS, SSAS TSQL etc.) : it is easier to mantain, explain and reuse and often if youre using very complex models you have to consider to much in further calculations. But anyway, your solution is great and I'm always pleased to learn new ways to accomplish such tasks from people like you.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |