Forum Discussion

campbellmurphy's avatar
campbellmurphy
Helper I
5 years ago
Solved

Alternative to DAX GENERATE Function in Power Query

Hi Everyone, I have two working tables (A & B) and the ideal transformed result table (C). I need to generate the same table within Power Query instead of DAX.  Does anyone know if this is possible ...
Need Help
  • ImkeF's avatar
    ImkeF
    5 years ago

    Hi campbellmurphy ,

    if you're interested in a more generic approach for this kind of wildcard matches, you can check out the file enclosed.
    It creates wildcard profiles for each kind of wildcard distribution and uses a simple join at the end. It should be fairly fast as long as there are not too many different wildcard profiles.

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

With a single query ... no need to combine

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZMxb8IwEIX/S2YkfHcEkpFS10itIjWhdSTEEKlDkSqGQv9/zw4ZfLYVlgy5T+/5PZ+Px0LXNTwBQLEoQCn+bv+ut9/h5zy4P0tVLlGhKk6LCUV44wl69qPbZigC7Ihn5LndcBm+koLkvHFOEJ2t4Uk5yn2fL3e1dcg9s5o1Sd+IRCatbnm6Gr1fMyCB44yd96fR34sm21zLWARkOszhm7BWd+bD3j6OE7xoX4doNwSxce22e5vsLWSpGXclKiLCMLtSG7kBHo2dK8kRfGJ0XZW8AZxuQISOQHrEFlySnaFVKnSE8qoYygavZO3AuE5sfwQStI2ey473dxwytVwKZ9r3fSpPLfMAvB8IyxlRcgfU/EjiJBN4+gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code (FK)" = _t, Balance = _t, Country = _t, Period = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code (FK)", type text}, {"Balance", Int64.Type}, {"Country", type text}, {"Period", type date}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Changed Type", "ID", each Text.Range([#"Code (FK)"],4,1)),
    #"Raggruppate righe" = Table.Group(#"Aggiunta colonna personalizzata", {"Period"}, {{"all", each Table.InsertRows(Table.Group(_,{"ID"},{"Balance Sum",(b)=> List.Sum(b[Balance])}),0,{[ID="?",#"Balance Sum"=List.Sum([Balance])]})}}),
    #"Tabella all espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "all", {"ID", "Balance Sum"}, {"ID", "Balance Sum"}),
    #"transfor col" = Table.TransformColumns(#"Tabella all espansa",{{"ID", each Record.Field(dict,_), type text}})
in
    #"transfor col"

 

 

 

 

where dict is:

 

 

 

you get this result

campbellmurphy's avatar
campbellmurphy
Helper I
5 years ago

Thanks Anonymous this looks

promising. I had some issues creating the dict table correctly. Is it possible for you to post the pbix file?

    • campbellmurphy's avatar
      campbellmurphy
      Helper I
      5 years ago

      Thanks Rocco, I really appreciate your assistance.  In the end I went with another solution but I agree that yours works and is a good alternative