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 a Web API endpoint which I want to pull data from it. It is protected with OAuth2.
Right now I can successfully pull the data from it but I have to specify the column names.
I want the column names to be dynamic.
The below code is working good:
let
GetAuth = Web.Contents("https://localhost/token",
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("username=username&password=password&grant_type=password")
]),
FormatAuthAsJson = Json.Document(GetAuth),
access_token = FormatAuthAsJson[access_token],
ReportData = Json.Document(Web.Contents("https://localhost/api/Search",
[
Headers=[Authorization="bearer "&access_token,
#"Content-Type"="application/json; charset=utf-8"]
,Content = Text.ToBinary("{""SearchCriteria"":""test""}")
])),
#"Converted Data To Table" = Table.FromList(ReportData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Report Data" = Table.ExpandRecordColumn(#"Converted Data To Table", "Column1", { "Id", "Name", "Description" })
in
#"Expanded Report Data"
When I change #"Expanded Report Data" to be like that:
#"Expanded Report Data" = Table.ExpandTableColumn(#"Converted Data To Table", "Column1", List.Union(List.Transform(#"Converted Data To Table"[Column1], each Table.ColumnNames(_))))
I got this error:
Expression.Error: We cannot convert a value of type Record to type Table.
Any help will be appricaited?
Solved! Go to Solution.
Hello
substitude this code
#"Converted Data To Table" = Table.FromList(ReportData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Report Data" = Table.ExpandRecordColumn(#"Converted Data To Table", "Column1", { "Id", "Name", "Description" })
with this one
#"Converted Data To Table" = Table.FromList(ReportData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
UniqueRecordNames = List.Union( List.Transform(#"Converted Data To Table"[Column1], each Record.ToTable(_)[Name])),
#"Expanded Report Data" = Table.ExpandRecordColumn(#"Converted Data To Table", "Column1", UniqueRecordNames )
this should to the trick. Was not able to test your code, but it should work
Have fun
Jimmy
Hello
substitude this code
#"Converted Data To Table" = Table.FromList(ReportData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Report Data" = Table.ExpandRecordColumn(#"Converted Data To Table", "Column1", { "Id", "Name", "Description" })
with this one
#"Converted Data To Table" = Table.FromList(ReportData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
UniqueRecordNames = List.Union( List.Transform(#"Converted Data To Table"[Column1], each Record.ToTable(_)[Name])),
#"Expanded Report Data" = Table.ExpandRecordColumn(#"Converted Data To Table", "Column1", UniqueRecordNames )
this should to the trick. Was not able to test your code, but it should work
Have fun
Jimmy
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.