• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

M code Power BI Dashboard overzetten naar Excel, hoe te doen...?

Status
Niet open voor verdere reacties.
W

whu2

Gebruiker
Lid geworden
22 aug 2008
Berichten
31
Hi,

Ik zit met het volgende probleem. Ik heb een Power BI file, waarmee middels een API data binnen gehaald wordt. Dit geheel wil ik min of meer nabouwen in Excel, gebruik makend van de M codes (waarbij in Power BI de tabellen worden aangemaakt). Hiervoor wordt gebruik gemaakt van een aantal parameters (die ik nu ook in Excel heb kunnen plaatsen), en meerdere tabellen die worden aangemaakt middels M code.
Ik loop hier tegen een probleem aan. In Power BI kan vrolijk naar een eerder aangemaakte tabel verwezen worden. In mijn geval wordt in de eerste tabel via de API een access token aangevraagd, die wordt aangeroepen in de code van de tweede tabel. Daarnaast worden de overige tabellen ook opgebouwd uit info uit de net eerder aangemaakte tabellen. Echter... in Excel worden geen tabellen aangemaakt middels M code...

Iemand een idee hoe je data geproduceerd met M code 'nummer 1', kan gebruiken in de volgende, M code 'nummer 2' etc.?

Ben erg benieuwd!!
 
Het gaat mij misschien niets aan maar om je vraag beter te begrijpen. Waarom wil je het nabouwen in Excel als je Power BI heb? En waar je schrijft "In Power BI kan vrolijk naar een eerder aangemaakte tabel verwezen worden." waar je tabel schrijft bedoel je waarschijnlijk een query?
 
huijb zei:
Het gaat mij misschien niets aan maar om je vraag beter te begrijpen. Waarom wil je het nabouwen in Excel als je Power BI heb? En waar je schrijft "In Power BI kan vrolijk naar een eerder aangemaakte tabel verwezen worden." waar je tabel schrijft bedoel je waarschijnlijk een query?
Klik om te vergroten...

Hi, dank voor je reactie! Ik wil dit in Excel nabouwen, gezien er op de plaats (werk) waar dit neergezet moet worden, PBI zodanig restricted is dat een verbinding met internet niet is toegestaan (API). Excel daarentegen is geen probleem...

Met tabel doel ik op een tabel welke in dit geval is aangemaakt middels een stuk M code (en, in dit geval, niet verder is 'behandeld').
 
Is het geen optie om gewoon de tabellen (niet in query editor) te kopieren om vervolgens te plakken in Excel.
 
Power Bi legt een connectie naar een database. als je die wil benaderen vanuit excel dan moet je een connectie naar de database maken vanuit Excel bijvoorbeeld via ODBC. alleen ken ik geen enkele collega die dat zou toelaten :eek:.
 
Via een ODBC verbinding connectie leggen naar je database, vervolgens de tabellen die je nodig hebt met queries uit de database halen.
Je kunt de tabellen (mits mogelijk) onderling met elkaar verbinden door relaties aan te maken (in Excel 2016 of eerdere versies), of via Power Query in Office 2019/2021 of Office 365).

Daarna draaitabellen aanmaken om je grafieken / KPI's weer te geven op de manier zoals je dat wilt.

Vervolgens kun je met VBA code en macro maken om je gegevens te vernieuwen via ODBC omdat deze niet 'live' in verbinding staan met de database.
Handig is dan om in een cel (of shape gelinked aan een cel) de datum, tijdstip en user te tonen om aan te geven naar welke gegevens je aan het kijken bent.
Bvb:
Code: 
Sheets("Blad1").Range("A1") = "Laatste update uitgevoerd op:  " & DateValue(Now) & "    " & TimeValue(Now) & "    -    " & "door:    " & Application.UserName
 
Laatst bewerkt:
huijb zei:
Is het geen optie om gewoon de tabellen (niet in query editor) te kopieren om vervolgens te plakken in Excel.
Klik om te vergroten...

Zou mooi zijn als dat zou kunnen! :) Helaas werkt kopieren en plakken niet tussen PBI en Excel.

Het probleem is ook dat een uitkomst uit query 1, welke benodigd is in query 2, leidt tot een foutmelding in Excel (niet in Power BI!): "[Experssion.Error] We cannot apply operator & to types Text and Table".

Het gaat om de volgende twee queries (ik verwacht geen problemen met de overige queries):

Query Access_token:

Code: 
let 

GetJson=Web.Contents(Link_getToken, 
[ 
   Headers=[#"Content-Type"="application/json"],
   Content=Text.ToBinary(
      "{ ""_username"": """&Username&""", ""_password"": """&Password&"""}"
   )
]
),

   FormatAsJson = Json.Document(GetJson),
   #"Converted to Table" = Record.ToTable(FormatAsJson),
   access_token_result = #"Converted to Table"{0}[Value]
in
    access_token_result


Second table (problem is: "...Authorization=" XXX "& Access_token..."):

Code: 
let 

GetJson_labels=Web.Contents(Link_getLabel,
[ 
   Headers=[#"Content-Type"="application/json",
   Authorization=" XXX "& Access_token
   ]
]
),
   FormatAsJson_labels = Json.Document(GetJson_labels),
... etc.
in
    #"Renamed Columns"
 
NoellaG zei:
Power Bi legt een connectie naar een database. als je die wil benaderen vanuit excel dan moet je een connectie naar de database maken vanuit Excel bijvoorbeeld via ODBC. alleen ken ik geen enkele collega die dat zou toelaten :eek:.
Klik om te vergroten...

Het is de bedoeling om gebruik te maken van een API, om zodoende data binnen te halen van een externe partij. Gelukkig (waarschijnlijk dan) geen verbinding met een lokale DB vanuit Excel. :D
 
Dan moet ik je toch teleurstellen, dat gaat erg goed kan ik je zeggen. Tenminste ik doet het met regelmaat al een jaar of 4.
 
BarryT zei:
Via een ODBC verbinding connectie leggen naar je database, vervolgens de tabellen die je nodig hebt met queries uit de database halen.
Je kunt de tabellen (mits mogelijk) onderling met elkaar verbinden door relaties aan te maken (in Excel 2016 of eerdere versies), of via Power Query in Office 2019/2021 of Office 365).

Daarna draaitabellen aanmaken om je grafieken / KPI's weer te geven op de manier zoals je dat wilt.

Vervolgens kun je met VBA code en macro maken om je gegevens te vernieuwen via ODBC omdat deze niet 'live' in verbinding staan met de database.
Handig is dan om in een cel (of shape gelinked aan een cel) de datum, tijdstip en user te tonen om aan te geven naar welke gegevens je aan het kijken bent.
Bvb:
Code: 
Sheets("Blad1").Range("A1") = "Laatste update uitgevoerd op:  " & DateValue(Now) & "    " & TimeValue(Now) & "    -    " & "door:    " & Application.UserName
Klik om te vergroten...

Het gaat om een verbinding met een API, om data binnen te halen. Hiervoor gebruik ik in eerste instantie 2 queries, waarbij de uitkomst van de eerste query (tabel Access_token) gebruikt zou moeten worden in de tweede query. In M code in Power BI geen probleem, maar Power BI kan ik hier dus niet gebruiken. Excel geeft hier dan de error: [Expressioni.Error] We cannot apply operator & to types Text and Table...


Query Access_token:

Code: 
let 

GetJson=Web.Contents(Link_getToken, 
[ 
   Headers=[#"Content-Type"="application/json"],
   Content=Text.ToBinary(
      "{ ""_username"": """&Username&""", ""_password"": """&Password&"""}"
   )
]
),

   FormatAsJson = Json.Document(GetJson),
   #"Converted to Table" = Record.ToTable(FormatAsJson),
   access_token_result = #"Converted to Table"{0}[Value]
in
    access_token_result

Tweede query (probleem is hier: "...Authorization=" XXX "& Access_token..."):

Code:
Code: 
let 

GetJson_labels=Web.Contents(Link_getLabel,
[ 
   Headers=[#"Content-Type"="application/json",
   Authorization=" XXX "& Access_token
   ]
]
),
   FormatAsJson_labels = Json.Document(GetJson_labels),
... etc.
in
    #"Renamed Columns"
 
Kan het probleem niet te maken hebben met het feit dat het paswoord geëncrypteerd is?
 
Laatst bewerkt:
NoellaG zei:
Kan het probleem niet te maken hebben met het feit dat het paswoord geëncrypteerd is?
Klik om te vergroten...

Nee, voor het password is een parameter opgegeven. Ik heb hier overigens eerder een testje gedaan door het password te wijzigen, maar dan komt er geen verbinding tot stand. Sowieso werkt het password, want de eerste response (de Access_token) komt gewoon binnen. Het is juist deze token die in de 2e query gebruikt moet worden. Maar... Excel geeft aan niet text met een tabel (uitkomst) te kunnen combineren...
 
huijb zei:
Dan moet ik je toch teleurstellen, dat gaat erg goed kan ik je zeggen. Tenminste ik doet het met regelmaat al een jaar of 4.
Klik om te vergroten...

In dat geval ben ik natuurlijk wel benieuwd hoe je dat doet! :) Slepen, CTRL-C etc. werkt niet (bij mij althans)?
 
Zo, zie bijlage. Copy tabel, paste in Excel. Let wel dit is, zoals ik eerder zei, niet in Query editor.
 

Bijlagen

  • Copy table.png
    Copy table.png
    19,9 KB · Weergaven: 17
huijb zei:
Zo, zie bijlage. Copy tabel, paste in Excel. Let wel dit is, zoals ik eerder zei, niet in Query editor.
Klik om te vergroten...

Ah, op die manier, hardcopy! Helaas kan ik deze dan niet (meer) updaten, wat men nu juist enkel in Excel wil doen. Maar een hele mooie tip, weer een manier erbij om alle data in 1 keer te kopieren, deze wist ik nog niet (normaal deed ik dit kolom voor kolom vanuit een tabel, of alles vanuit een visual)!
 
huijb zei:
Zelf werk ik er niet zoveel mee maar is Power Query geen oplossing voor je?
https://docs.microsoft.com/nl-nl/power-query/power-query-what-is-power-query
https://support.microsoft.com/nl-nl...in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a
Klik om te vergroten...

Ja, de queries die ik gebruik (en ook hier gepost zijn) worden alle in Power Query mode in Excel geplaatst; iets binnen deze queries loopt in Excel niet helemaal (of: helemaal niet...) goed, terwijl dit binnen Power BI wel het geval is...
 
Als ik zoek op deze foutmelding
Code: 
[COLOR=#333333][Experssion.Error] We cannot apply operator & to types Text and Table[/COLOR]
lijkt het er op dat er ergens toch een typefout is gemaakt. Bijvoorbeeld, een operator die gebruikt wordt in Power BI niet gebruikt kan worden in Power Query.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan