Forum Discussion

unknown917's avatar
unknown917
Helper IV
5 months ago
Solved

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 ta...
Tips and Tricks
  • MFelix's avatar
    MFelix
    5 months ago

    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's avatar
MFelix
Super User
5 months ago

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's avatar
unknown917
Helper IV
5 months ago

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

HUBGROUPSeq OrderTYPESEQUENCE 
separate Table(static)separate Table(static)(starting #)1st Return
3211700132117001
4561320145613201
  • MFelix's avatar
    MFelix
    Super User
    5 months ago

    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's avatar
      unknown917
      Helper IV
      5 months ago

      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's avatar
        MFelix
        Super User
        5 months ago

        Hi unknown917 ,

         

        Apologies for the late response,

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