M Power Query - Expand Columns Dynamically

Question

I'm trying to expand columns after converting to a table, but these columns need to be expanded dynamically.

I've researched online but the code I'm adding just does not work one way or another. I must be missing something but i am not sure.

This is my code:

let
Date = DateTime.ToText((DateTime.LocalNow()), "yyyy-MM-dd"),
Date2 = Date.ToText((Date.AddDays(Date.From(DateTime.FixedLocalNow()),-90)), "yyyy-MM-dd"),
Source = Json.Document(Web.Contents("https://www.findmyshift.com/staff-view?r=2rr67u5y9i&apikey=***********&format=json&start=" & Date2 & "&finish=" & Date)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", List.Accumulate(#"Converted to Table"[Column1], {}, (state, current) => List.Union({state, Table.ColumnNames(current)}))),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Column1","&#39;","'",Replacer.ReplaceText,{"Column1.Name"}),
#"Renamed Columns" = Table.TransformColumnNames(#"Replaced Value", (columnName as text) as text => Text.Replace(columnName, "Column1.", ""))

Any help would be greatly appreciated.

score 0 · Answer 1 · answered Jan 11 '19 at 16:27

0

Try to replace step #"Expanded Column1" by following code:

= Table.ExpandRecordColumn(#"Converted to Table", "Column1",
Record.FieldNames(#"Converted to Table"{0}[Column1]))

answered Jan 11 '19 at 16:27

Aleksei Zhigulin

1,594
1
8
11

Thank you for the reply, but i get an error message: **Expression.Error: We cannot convert a value of type Record to type Table.** – Hulk Smash 93 Jan 11 '19 at 16:36
This error actually appeared with your initial code, but after replacement, which I suggested, all works fine. So you could try again: – Aleksei Zhigulin Jan 12 '19 at 21:27

score 0 · Accepted Answer · answered Jan 11 '19 at 21:55

I think you should remove your API key from your question, you probably don't want people to misuse/access this service using your credentials. (It might be good to ask the service provider to issue you a new API key as replacement, so that nobody can use the one in your answer.)

If you replace "REPLACE ME WITH YOUR API KEY" (in the code below) with your actual API key (between double quotes), then this code should do what you were trying to do.

let
    startDate = Date.ToText((Date.AddDays(Date.From(DateTime.FixedLocalNow()),-90)), "yyyy-MM-dd"),
    endDate = DateTime.ToText((DateTime.LocalNow()), "yyyy-MM-dd"),
    jsonResponse =
        let
            response = Web.Contents("https://www.findmyshift.com/staff-view", [Query = [r = "2rr67u5y9i", apikey = "REPLACE ME WITH YOUR API KEY", format = "json", start = startDate, finish = endDate]]),
            json = Json.Document(response)
        in
            json,
    toTable = Table.FromRecords(jsonResponse),
    replaceValue = Table.ReplaceValue(toTable, "&#39;", "'", Replacer.ReplaceText, Table.ColumnNames(toTable)) // Replace in all columns
in
    replaceValue

Table.FromRecords can do what you were trying to do (without all the dynamic column expanding and renaming)
Web.Contents can build the query string for you (and will automatically handle URL encoding) provided you pass an argument to the Query parameter.

M Power Query - Expand Columns Dynamically

2 Answers2