Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
)
)
)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
)
)
)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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?
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?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Here is the screenshot of error
I am using Visual Studio to create custom connector you can see error in the "Errors" tab.
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:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
}
]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.