Forum Discussion

jeroenterheerdt's avatar
jeroenterheerdt
Microsoft Employee
9 years ago

Power Query "looping"

Hi all,

 

I am trying to build a custom PQ connector for a data source that provides a number of data tables and related dimensions. Ideally I would like the user to enter the name of the table to retrieve and the connector needs to return a flattened table of the data and its dimensions.

 

I have the following input (with more rows obviously):

 

Data table

 

IDDimension1Dimension2SalesAmount
0Dim1_ADim2_Z50

 

Dimensions table

 

DimensionNameDimensionKeyDimensionValue
Dimension1Dim1_AMyCompany
Dimension 2Dim2_ZMyProduct

 

 What I would like to return is the following:

 

IDDimension1Dimension2SalesAmount
1MyCompanyMyProduct50

 

I know, this is easy enough by just loading the separate table and then creating a relationship between the tables. I do not want to present all the tables to the user, I want them all in one table. Also the number of dimensions will change from table to table (I can query the list of dimensions from the system however). Any given data table might have one or more dimensions that will need to be combined together into the resulting table. Essentially what I would like to build is a dynamic lookup responsive of the number of dimensions of the data table. 

 

In "normal" programming languages I would approach this using something like a foreach, however this obviously does not exist in PQ.

 

Any one that can help me solve this puzzle? Thanks!

Need Help

7 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    Do the dimension-tables have a direct relationship to the main table or are there multiple levels to be expanded/connected (snowflake-structure)?

    Does the data come from a SQL-server?

    • jeroenterheerdt's avatar
      jeroenterheerdt
      Microsoft Employee
      9 years ago

      No, the data does not come from SQL server otherwise I would not have this issue. The tables have a relationship to the main table. However, I need to solve this in PQ, not in PP and I do not know the exact tables beforehand, since it is dependent on a parameter in the PQ

      • ImkeF's avatar
        ImkeF
        Community Champion
        9 years ago

        You need an "AllocationTable", that holds the names of the tables and the columns through which they are connected (see in example below).

         

        This example flattens all tables that are connected to what you choose in "SelectedTable" from your local AdventureWorks-DB. Watch out: The "SelectedTable" needs to exist in column "FromTable" from "AllocationTable"!

         

        let

// Parameters
Database = Sql.Databases("localhost", [CreateNavigationProperties=false]){[Name="AdventureWorksDW2012"]}[Data],
AllocationTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZPBboMwEET/hXN+oipNFPWQqukt4mC5q8oSXqNlfSBfH0iwsReIkvS4M/NGsAunU1Ea+2Gb2nUAxaY4qhraHyAy7Kj7hK7X+kQuL+aqzbXrQH8KzVmxcdjn3j0RoJ6agiC8gd4qzVuDCvXwJG9aO48cwXHOnTlWKobIDEOiLaShUcQWkHfkfDOBub6cnNelLx+7xEZkZt5y1ICKjJu2PwrCC+QeGQiBrzd5YedzvmVngRL+JghvjS89PHIFiX2R+/V6Ovg4584d2Lps7VGR7lrF/z79ofEbWqhroFcPIfkHFymx8DtHLvm/U2+Nf+oQC/Czh5AVYY4NQRBeVV0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FromTable = _t, FromName = _t, ToTable = _t, ToColumn = _t]),
SelectedTable = "FactInternetSales",
MainTable = "FromTable",
IDMain = "FromName",
LookupTable = "ToTable",
IDLookup = "ToColumn",

// Filter User Selection
    FilterMainTable = Table.SelectRows(AllocationTable, each Record.Field(_, MainTable)=SelectedTable),

// Create row number for iterating
    AddIndex = Table.AddIndexColumn(FilterMainTable, "Index", 0, 1),

// Expanded Columns will be prefixed with name of the table
    ColumnNames = Table.Buffer(Table.AddColumn(AddIndex, "RenameHeaders", each {Table.ColumnNames(Database{[Name=Record.Field(_, LookupTable)]}[Data]),List.Transform(Table.ColumnNames(Database{[Name=Record.Field(_, LookupTable)]}[Data]), (ListItem)=> Record.Field(_, LookupTable) &"."& ListItem)})),

FlattenedTable =

// We only need the last element of the list created
                List.Last(

// Generates a list of nested tabes, looping through the filtered AllocationTable
                List.Generate(()=>    

// StartValue
                [Result = Database{[Name=SelectedTable]}[Data], Counter=-1],

// While-condition
                each [Counter] < Table.RowCount(ColumnNames), 

// Evaluated Expression: Lookup previous Result with new table and expand columns
                each [      Lookup= Table.NestedJoin([Result],{Record.Field(FilterMainTable{Counter}, IDMain)}, Database{[Name=Record.Field(FilterMainTable{Counter}, LookupTable)]}[Data],{Record.Field(FilterMainTable{Counter}, IDLookup)},"LookupTable",JoinKind.LeftOuter),
                            Result= Table.ExpandTableColumn(Lookup, "LookupTable", Record.Field(ColumnNames{Counter}, "RenameHeaders"){0},Record.Field(ColumnNames{Counter}, "RenameHeaders"){1}),
                            Counter = [Counter]+1
        ]
))[Result]

in
    FlattenedTable