Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic Column Names

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?

  • 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

2 Replies

Replies have been turned off for this discussion
  • Jimmy801's avatar
    Jimmy801
    Community Champion

    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

    • Anonymous's avatar
      Anonymous
      Not applicable

      That is perfect Jimmy801  thanks a million.