JustinAiken Tracker

Re: Connecting to an API

JustinAiken — Fri, 11 Sep 2020 20:23:51 GMT

If you use a scheduled refresh for this API, you may need to make the following alteration. Credit to Chris Webb.

Final = List.Generate
(
()=>
[
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/", [RelativePath="0", Headers=[Accept="application/json"]])),
TotalCount = 1
],
each [TotalCount]>0,
each [
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/", [RelativePath= Text.From([Result][MetaCollectionResult][LastTimeStamp]), Headers=[Accept="application/json"]])),
TotalCount = [Result][MetaCollectionResult][TotalCount]
],
each [Result][Clients]
)

Thank you Jimmy801

Re: Ampliación de la lista de registros en columnas

JustinAiken — Wed, 09 Sep 2020 19:08:47 GMT

Desafortunadamente no puedo proporcionar acceso a la API de forma anónima. Sin embargo, después de eliminar algunas columnas por brevedad, aquí está la vista de mi GET:

Después de expandir "CustomFields" a New Rows, obtengo las 44 filas de lo siguiente:

Después de expandir "CustomFields", obtengo lo siguiente:

Este es un cliente ficticio, por lo que no hay valores en la columna Valores para esto. Pero la columna Valor tendrá valores de texto. Desafortunadamente, hay algunos valores nulos que tendrán que filtrarse.

Re: Expanding List of Records into Columns

JustinAiken — Wed, 09 Sep 2020 19:07:16 GMT

Unfortunately I can't provide access to the API anonymously. However, after removing some columns for brevity's sake, here is the view from my GET:

After expanding "CustomFields" to New Rows, I get the 44 rows of the following:

After expanding "CustomFields," I get the following:

This is a dummy client, so there are is no values in the Values column for this. But the Value column will have text values. Unfortunately, there are some null values that will have to be filtered out.

Re: Ampliación de la lista de registros en columnas

JustinAiken — Wed, 09 Sep 2020 17:34:02 GMT

Gracias @edhans y @ImkeF por sus sugerencias rápidas. Como mi HTTP GET no contiene ninguna información confidencial, la he copiado a continuación. Dado que el sistema solo permite 50 filas por llamada, la fórmula utiliza LastTimeStamp para obtener los más de 2.000 registros.

Dejar
Final: List.Generate
(
()>
[
Resultado de Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/0", [Encabezados"Aceptar"aplicación/json"]])),
TotalCount n.o 1
],
cada [TotalCount]>0,
cada uno de ellos [
Resultado de Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/" & Text.From([Result][MetaCollectionResult][LastTimeStamp]), [Headers-[Aceptar"application/json"]])),
TotalCount á [Resultado][MetaCollectionResult][TotalCount]
],
cada [Resultado][Clientes]
),
FinalTable - Table.FromRecords(List.Combine(Final))
En
FinalTable

Si fuera a usar su enfoque @ImkeF,¿dónde introduciría su fórmula? Soy un poco novato con M.

Re: Expanding List of Records into Columns

JustinAiken — Wed, 09 Sep 2020 17:32:57 GMT

Thank you edhans and ImkeF for your quick suggestions. As my HTTP GET does not contain any sensitive information, I have copied it below. Because the system only allows 50 rows per call, the formula utilizes the LastTimeStamp to obtain all 2,000+ records.

let
Final = List.Generate
(
()=>
[
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/0", [Headers=[Accept="application/json"]])),
TotalCount = 1
],
each [TotalCount]>0,
each [
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/" & Text.From([Result][MetaCollectionResult][LastTimeStamp]), [Headers=[Accept="application/json"]])),
TotalCount = [Result][MetaCollectionResult][TotalCount]
],
each [Result][Clients]
),
FinalTable = Table.FromRecords(List.Combine(Final))
in
FinalTable

If I was to use your approach ImkeF, where would I input your formula? I am a bit of a novice with M.

Ampliación de la lista de registros en columnas

JustinAiken — Fri, 04 Sep 2020 22:04:00 GMT

He pasado por otras respuestas de la comunidad y videos sobre la expansión de los valores de los registros. BI Elite fue útil en lo que respecta a la expansión dinámica de registros.

Sin embargo, no he podido encontrar una solución que logre mi objetivo previsto. Después de mi conexión a una API con el CRM de nuestra empresa, y el JSON posterior, me quedo con una columna que contiene una lista:

Que contiene 44 registros por fila cuando se expande:

Cada registro contiene un campo y un valor. Si es posible, me gustaría expandir la lista en columnas nombradas por el campo y rellenar las columnas con cada valor. Cualquier ayuda sería muy apreciada!

Expanding List of Records into Columns

JustinAiken — Fri, 04 Sep 2020 22:02:36 GMT

I have gone through other community responses and videos regarding expanding record values. BI Elite was helpful in regards to the dynamic expansion of records.

However, I have been unable to find a solution that achieves my intended goal. Following my connection to an API to our company's CRM, and subsequent JSON, I am left with a column that contains a list:

Which contains 44 records per row when expanded:

Each Record contains one Field and Value. If possible, I would like to expand the list into columns named by the Field and populate the columns with each Value. Any help would be greatly appreciated!

Re: PBIX file corrupted

JustinAiken — Wed, 10 May 2017 17:47:58 GMT

I had this problem today. Fortunately I published the report with almost all of my work the previous day, so I just downloaded the report from the Power BI online app and refreshed. This would lose any measures created since the previous publish, but serves as a backup.

So, if you have not published your work, it might be a best practice to do so regularly as it serves as a backup.