Forum Discussion
How to get Columna name from list using mQuery power query
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?
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
)
)
)
8 Replies
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
)
)
)
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?
