transform a column for use as array in other PowerQuery commands

[bartplessers]

Hi,

I have an excel where power query does some manipulations. The idea is that I provide a certain table, where the user can create a "mapping", for example:

In this table,

NR: the sort order

DATADEF: a list of column names of the final result

TYPE: the type of information (string, integer, ...)

SOURCE1: a list of column names of data comming from source 1

SOURCE2: a list of column names of data comming from source 2

In powerquery, I want i.e.

- rename all the columns from source 1 to the names defined in DATADEF

- change the type of the columns

- order the rows

- ...

This is now done by manually enumerating the JSON, ie

(myTable) => 
    let
        #"Appended Query" = Table.Combine({myTable, DATADEF}),
        #"Changed Type" = Table.TransformColumnTypes(#"Appended Query",{
            
            {"Capakey", type text}, 
            {"Partnumber", type text}, 
            {"Naam Perceel", type text}, 
            {"Perceel Straat", type text}, 
            {"Perceel Nummer", type text}, 
            {"Perceel Postcode", Int64.Type}, 
            {"Perceel Gemeente", type text}, 
            {"Perceel Provincie", type text}, 
            {"Perceel Begindatum", type date}, 
            {"Perceel Einddatum", type date}, 
            {"Perceel Gewijzigd op", type text}, 
            {"Perceel icon", type text}, 
            {"Adres Begindatum", type date}, 
            {"Adres Einddatum", type date}, 
            {"Adres Gewijzigd op", type text}, 
            {"Gebouw Begindatum", type date}, 
            {"Gebouw Einddatum", type date}, 
            {"Gebouw gewijzigd op", type text}, 
            {"Gebouwnr", type text}, 
            {"Omschr. gebouw", type text}, 
            {"% Op perceel", type number}, 
            {"KI Begindatum", type date}, 
            {"KI Einddatum", type date}, 
            {"KI gewijzigd op", type text}, 
            {"Bedrag KI", Int64.Type}, 
            {"Code KI", Int64.Type}, 
            {"KI Code Omschrijving", type text}, 
            {"Code Belastbaar KI", type text}, 
            {"Belastbaar Ki Omschrijving", type text}, 
            {"Ingangsdatum Gewijzigd KI", type date}, 
            {"Kadastrale Oppervlakte in M2", Int64.Type}, 
            {"Toestand Begindatum", type date}, 
            {"Toestand Einddatum", type date}, 
            {"Toestand Gewijzigd op", type text}, 
            {"Code Leegstand", Int64.Type}, 
            {"Leegstand sinds", type date}, 
            {"Bestemming", Int64.Type}, 
            {"Omschrijving Toestand", type text}, 
            {"(Deels) Verhuurd", Int64.Type}, 
            {"Omschrijving Verhuurd", type text}, 
            {"Omschrijving melding leegstand", type text}, 
            {"Aard Perceel", Int64.Type}, 
            {"Omschrijving Aard Perceel", type text}, 
            {"ZR Begindatum", type date}, 
            {"ZR Einddatum", type date}, 
            {"ZR Gewijzigd op", type text}, 
            {"Code Zakelijk Recht", type text}, 
            {"Eigenaar Kadastraal Inkomen", type text}, 
            {"Aandeel Zakelijk Recht", type text}, 
            {"Ondernemingsnummer", type text}, 
            {"Omschrijving Zakelijk Recht", type text}, 
            {"Datum creatie kadasterwijziging", type date}, 
            {"Soort kadasterwijziging", Int64.Type}, 
            {"Opmerking kadasterwijziging", type text}, 
            {"Gewijzigd door", type text}, 
            {"Omschrijving kadasterwijziging", type text}, 
            {"Dossiernr", type text}, 
            {"Naam overledene", type text}, 
            {"Aanvaardingsdatum", type date}, 
            {"Verkoopprijs", type number}, 
            {"Kredietnummer", type text}, 
            {"Info Onroerend Goed", type text}, 
            {"Begunstigde legataris", type text}, 
            {"KI Valuta", type text} 
        }, "en-US"),
        #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{
            "Capakey",
            "Partnumber",
            "Naam Perceel",
            "Perceel Straat",
            "Perceel Nummer",
            "Perceel Postcode",
            "Perceel Gemeente",
            "Perceel Provincie",
            "Perceel Begindatum",
            "Perceel Einddatum",
            "Perceel Gewijzigd op",
            "Perceel icon",
            "Adres Begindatum",
            "Adres Einddatum",
            "Adres Gewijzigd op",
            "Gebouw Begindatum",
            "Gebouw Einddatum",
            "Gebouw gewijzigd op",
            "Gebouwnr",
            "Omschr. gebouw",
            "% Op perceel",
            "KI Begindatum",
            "KI Einddatum",
            "KI gewijzigd op",
            "Bedrag KI",
            "Code KI",
            "KI Code Omschrijving",
            "Code Belastbaar KI",
            "Belastbaar Ki Omschrijving",
            "Ingangsdatum Gewijzigd KI",
            "Kadastrale Oppervlakte in M2",
            "Toestand Begindatum",
            "Toestand Einddatum",
            "Toestand Gewijzigd op",
            "Code Leegstand",
            "Leegstand sinds",
            "Bestemming",
            "Omschrijving Toestand",
            "(Deels) Verhuurd",
            "Omschrijving Verhuurd",
            "Omschrijving melding leegstand",
            "Aard Perceel",
            "Omschrijving Aard Perceel",
            "ZR Begindatum",
            "ZR Einddatum",
            "ZR Gewijzigd op",
            "Code Zakelijk Recht",
            "Eigenaar Kadastraal Inkomen",
            "Aandeel Zakelijk Recht",
            "Ondernemingsnummer",
            "Omschrijving Zakelijk Recht",
            "Datum creatie kadasterwijziging",
            "Soort kadasterwijziging",
            "Opmerking kadasterwijziging",
            "Gewijzigd door",
            "Omschrijving kadasterwijziging",
            "Dossiernr",
            "Naam overledene",
            "Aanvaardingsdatum",
            "Verkoopprijs",
            "Kredietnummer",
            "Info Onroerend Goed",
            "Begunstigde legataris",
            "KI Valuta"
        }),
        Result = #"Reordered Columns"
    in
        Result

I'm sure this can done smarter! 🙂

So basically I have all this information in my excel table. End user can define sortorder (NR), column names (DATADEF), type (TYPE), etc...

I created some calculated fields in Excel:

 and copy paste this JSON-string in my PowerQuery function manually.

Much better would be that PowerQuery reads a certain column and converts this to the above format.

Is this possible?

How?

Some sample code?

thanx

bartplessers

[bartplessers]

Well, somethings writing the problem down solves the problem itselfs...

This did the trick:

and now I can use this in (dynamically) "change type" queries like

cheers!,

bartplessers

[spinfuzer]

That works better and is easier.   I tried and must have had a typo when testing Expression.Evaluate because I was getting errors when I was first doing it so that's why I did the #shared thing.

[bartplessers]

Hi @spinfuzer 

Thanx for prompt reply!

I couldn't get your code working, don't understande were you are referring to with #shared in:

shared_table = Table.Buffer(Record.ToTable(#shared)),

However, If I do understand it correctly, you create a "nested" list:

a list were each value contains another list.

I tried to do this in another way:

let
    #"Source" = Excel.CurrentWorkbook(){[Name="DATADEF"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [TYPE] <> null and [TYPE] <> ""),
    #"Added Column" = Table.AddColumn(#"Filtered Rows", "myColumn", each {[NAME],[TYPE]}),
    #"myList" = #"Added Column"[myColumn],
    #"Result"= #"myList"
in
    #"Result"

Here I create a custom column "myColumn", with a calculated key/value pair {[NAME],[TYPE]}

This seems to work and -if I understood your code correctly- will give me the same result.

Above method works perfect for re-naming  because creating a list with { [OLDNAME],[NEWNAME]} result in a list of key/value pairs that are both strings.

However, if I want to use this technique to change types, I receive following error:

This is because my (nested) list contains {[NAME],[TYPE]} and both are interpreted as strings, while [TYPE] should be interpreted as a typecast object, not as a string....

So basically what is happening here is:

and not

Is there a way to let powerquery "evaluate" the string "type date" to typecasting 'type date'

Should I dig further into your code because maybe I missed something and your list solves the above problem?

kind regards,

bartplessers

[spinfuzer]

You can use List.Generate.  It can create a list of anything you put in the 4th argument.  In this case you want a list of lists.  You need to change your types though to match Text.Type and such.  I am going to the #shared records and then finding the name that matches Text.Type and then taking the value.  I am not sure of any other way to convert the text into a Type.  Below is how to change the column types.  Changing column names is pretty much the same except you don't need the shared table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    shared_table = Table.Buffer(Record.ToTable(#shared)),
    change = 
    List.Generate(
        () => [n = 0], // iterator
        each  [n] < Table.RowCount(DATADEF), // end condition
        each [n = [n] + 1], // next iterator
        each { DATADEF[DATADEF]{[n]}, Table.SelectRows(shared_table, (r) => r[Name] = DATADEF[TYPE]{[n]}){0}[Value]}
        ) // optional output if iterator is not the desired output
    ,
    transform = Table.TransformColumnTypes(Source, change )
in
    transform