How to get Columna name from list using mQuery power query

[vikas-jk]

I have data coming from api and then converted as List using mQuery.

Now I want to get column names from api ( column names are dynamic), how do I get it.

Expand= List.Generate(()=>
                   [Result= try GetData(1,2) otherwise null],
                each [Result] <> null,
                each [Result = try GetData(1,2) otherwise null],
                each [Result])

Where getData is a function

             GetData =(page as number, lastpage as number) =>
                  if page <= lastpage then
                 let
                      MainString= Text.Combine({"https://portal.example.com/v4_6_release/apis/3.0/company/contacts?page=1"}),
           
         

         
            Source = Json.Document(Web.Contents( MainString,  
                                [ManualCredentials = true,
                             Headers = [#"Authorization" = Text.Combine({"Basic ", "text"}), #"clientid" = "CliendId"]]))
    
                     in
            Source

    else null;

Now, how do I get columns name, so i can convert List into table and then expand all columns,

If I know columns names, I do this like this

tableOfPages = Table.FromRecords(Expand, Splitter.SplitByNothing(), "Column1"),
ExpandAll=  Table.ExpandTableColumn(tableOfPages, "Column1", {"id","firstName","lastName"})

Where

{"id","firstName","lastName"}

are columns, but what if I don't know columns, how do get column names from List generated or above?

[Greg_Deckler]

@ImkeF 

[ImkeF · Accepted solution]

Hi @vikas-jk ,

if all tables have the same name you can grab it from the first table like so:

Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(tableOfPages[Column1]{0}) )

otherwise you have to query over whole column like so:

Table.ExpandTableColumn(

    tableOfPages,

    "Column1",
    List.Distinct(

        List.Transform(

            tableOfPages[Column1],

            Table.ColumnNames

        )

    )

)

[vikas-jk]

Not Sure, why this solution is marked as accepted it didn't worked for me.

As you have asked to show you JSON, here is the demo JSON, which I am getting from server.

[
    {
        "id": 1,
        "firstName": "User 1",
        "lastName": "Last name",
       
      
    },
    {
        "id": 2,
        "firstName": "User 2",
        "lastName": "lastname 2"
       
    }
       
]

Note: I am getting lots of pages of data and using List.Generate with function to get all pages.

[vikas-jk]

I tried both of your above solution, getting same error in both "we cannot convert value of type list into table"

shared ColumnsCheck.Contents = (optional message as text) =>
       let      
       
  Expand= List.Generate(()=>
                   [Result= try GetData(1,2) otherwise null],
                each [Result] <> null,
                each [Result = try GetData(1,2) otherwise null],
                each [Result]),
            tableOfPages = Table.FromList(Expand, Splitter.SplitByNothing(), {"Column1"}),
output=Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(tableOfPages[Column1]{0}) )
               
        in
             output;

What is wrong with the above query?

[ImkeF]

Hi @vikas-jk ,

then the column you're trying to expand doesn't contain what I expected it to contain.

If it would contain tables, my code would work as you can see if you paste this code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSixOUdJRSi0qUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    Custom1 = #table({"Column1"}, {{#"Changed Type"}, {#"Changed Type"}}),
    #"Expanded Column1" = Table.ExpandTableColumn(Custom1, "Column1", Table.ColumnNames(Custom1[Column1]{0}))
in
    #"Expanded Column1"

It is very hard to debug without seeing the data. 
Could you share a screenshot of the data before the step that produces the error?

[vikas-jk]

Here is the screenshot of error

I am using Visual Studio to create custom connector you can see error in the "Errors" tab.

[ImkeF]

Hi @vikas-jk ,

I still believe that it has something to do with what your query returns (as my code does what the example showed).

So please adjust your connector and show what's actually returned as an input for the next step:

[vikas-jk]

This is sample JSON data, which is returned from server. There are lots of  user's I have shown you only 2 out of 100.

[
    {
        "id": 1,
        "firstName": "User 1",
        "lastName": "Last name",
       
      
    },
    {
        "id": 2,
        "firstName": "User 2",
        "lastName": "lastname 2"
       
    }
       
]