Create rows from 2 tables

[unknown917]

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.

[ralf_anton]

Hi, 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"

[Natarajan_M]

Hi @unknown917 , Could you please provide sample data for both tables along with the expected output so that the community can assist you better?
Thanks 

[MFelix]

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"

[unknown917]

@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

[MFelix]

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.

[unknown917]

@MFelix - thank you so much for the help, it is beyond appreciated.  I have figured a way to do it in the 1 table, just repeated the unique keys (I know could have done it more efficiently I'm sure)  but I have run into a snag with an a key entry that only requires a single output instead of 70.  Below, the intend result is 32117888, but I am not getting a return.  What am I missing?

[Hub] * 100000 + 2 * 10000 + [Seq Order] * 1000 + 8 * 100 + 88 .. [
Hub
]
* 100000
+ 2 * 10000
+ [Seq Order] * 1000
+ 8 * 100
+ 1

[MFelix]

Hi @unknown917 ,

Apologies for the late response,

Can you please provide the full code so I can check it.

[unknown917]

Thank you @MFelix - I was able to figure it out on my own...it was so simple I overlooked it

[v-karpurapud]

Hi @unknown917 

We're pleased to hear that you found the cause and fixed the issue.  If you have any further questions, feel free to reach out and we'll be glad to assist.

Regards,

Microsoft Fabric Community Support Team.

[MFelix]

Hi @unknown917 ,

Glad you were able to figure it out.

Don't forget to accept the right answer so it can help others, even if it's your own.