Forum Discussion
Create rows from 2 tables
I have a refreshable table pulling in "Hub" & "Subset". I have a "key" table with different criteria based on different options. This is currently finite and probably brought in through an excel table. Or, perhaps the code can be built to eliminate the need for the 2nd table?
I am in need of creating a finite number of rows based on the refreshable table & key table so it can be filtered in the PBI visual.
Here is an example of the items needed from the combined key table & data to be used from the refreshable table. The desired result is to produce rows from each row of the key table. Example would be to produce say 70 rows of each unique concat. Example: Hub = 321, Subset = 7 therefore, 32117001, 32117002, 32117003...32117070; 32117201, 32117202, 32117203...32117270; 32117401, 32117402...32117470; 32117601...32117670
| HUB | GROUP | SUBSET | TYPE | SEQUENCE |
| (dependent) | (static) | (dependent) | (static) | (starting #) |
| 1 | 0 | 01 | ||
| 1 | 2 | 01 | ||
| 1 | 4 | 01 | ||
| 1 | 6 | 01 |
Any help would be truy appreciated.
Hi unknown917 ,
Don't know if you have any column to add the number of rows in this case the 70 you can built a formula similar to this:
{ [Hub]*1000000 + [Groupsubset]*1000 + 1.. [Hub]*1000000 + [Groupsubset]*1000 + [Number of rows]}Full code below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYyVNJRMgdhA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hub = _t, Groupsubset = _t, #"Number of rows" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Hub", Int64.Type}, {"Groupsubset", Int64.Type}, {"Number of rows", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each { [Hub]*1000000 + [Groupsubset]*1000 + 1.. [Hub]*1000000 + [Groupsubset]*1000 + [Number of rows]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
#"Expanded Custom"
10 Replies
Hi unknown917 ,
Don't know if you have any column to add the number of rows in this case the 70 you can built a formula similar to this:
{ [Hub]*1000000 + [Groupsubset]*1000 + 1.. [Hub]*1000000 + [Groupsubset]*1000 + [Number of rows]}Full code below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYyVNJRMgdhA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hub = _t, Groupsubset = _t, #"Number of rows" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Hub", Int64.Type}, {"Groupsubset", Int64.Type}, {"Number of rows", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each { [Hub]*1000000 + [Groupsubset]*1000 + 1.. [Hub]*1000000 + [Groupsubset]*1000 + [Number of rows]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
#"Expanded Custom"MFelix - Allow me to explain a little more clearly. Is it possible to merge 2 tables that don't have a primary key? There may be several hubs with or without multiple subsets ("Seq Order") but each hub needs to have 70 rows produced for each unique row in the "Key" table. I have 20 unique keys to be applied
HUB GROUP Seq Order TYPE SEQUENCE separate Table (static) separate Table (static) (starting #) 1st Return 321 1 7 0 01 32117001 456 1 3 2 01 45613201 Hi unknown917 ,
You can join the two tables yes, but can you please give me an example of the values on both tables. So I can give you the better code for that.
Hi unknown917 , Could you please provide sample data for both tables along with the expected output so that the community can assist you better?
ThanksHi, bin mir nicht sicher, ob ich Dich richtig verstanden habe. Wenn Du es so meinst (Ausschnitt)
Dann kannst Du das mit folgendem Code erreichen:
let Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"HUB", type text}, {"GRUPPE", type text}, {"Seq-Ordnung", type text}, {"TYP", type text}}), #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "Start", each [HUB] & [GRUPPE] & [#"Seq-Ordnung"] & [TYP] & "1"), #"Geänderter Typ2" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte",{{"Start", Int64.Type}}), #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Geänderter Typ2", "Ende", each [Start]+69), #"Hinzugefügte benutzerdefinierte Spalte3" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte2", "Abfolge", each {[Start]..[Ende]}), #"Erweiterte Liste" = Table.ExpandListColumn(#"Hinzugefügte benutzerdefinierte Spalte3", "Abfolge"), #"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte Liste",{"Start", "Ende"}) in #"Entfernte Spalten"
