Advertisement
Guest User

Untitled

a guest
Mar 24th, 2017
98
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.55 KB | None | 0 0
  1. let
  2. Source = Excel.Workbook(File.Contents("D:\Downloads\FactTransactions.xlsx"), null, true),
  3. Transactions_Table = Source{[Item="Transactions",Kind="Table"]}[Data],
  4. FullTransactions = Table.TransformColumnTypes(Transactions_Table,{{"Time", type datetime}, {"Value", Int64.Type}}),
  5.  
  6. // Meta Query
  7. // Function that gets the port number of the $Embedded$ tabular model
  8. MSMDSRVport = () => let cu=Table.FirstN(Table.Sort(Folder.Contents("C:\Users"),{{"Date accessed",Order.Descending}}),1)[Name]{0} in List.Transform(Table.SelectRows(Folder.Files("C:\Users\"&cu&"\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces"),each [Name]="msmdsrv.port.txt")[Content],each Lines.FromBinary(_,null,null,1200){0}),
  9. // Get $Embedded$ port number
  10. Port = Table.FromList(MSMDSRVport(),null,{"port"}){0},
  11. // Get the database/catalog name
  12. Catalog = AnalysisServices.Databases("localhost:"&Port[port])[Name]{0},
  13. // Get the contents of QueryLog table from tabular model
  14. PrevTransactionsRaw = AnalysisServices.Database("localhost:"&Port[port],Catalog,[Query="EVALUATE (Transactions)"]),
  15. // Clean the column names to match the table in Power Query
  16. PrevTransactions = Table.RenameColumns(PrevTransactionsRaw ,{{"Transactions[Time]","Time"},{"Transactions[Value]","Value"}}),
  17.  
  18. // Find the latest Timestamp
  19. MaxTime = Table.Last(PrevTransactions)[Time],
  20. // Select Delta records
  21. DeltaTransactions = Table.SelectRows(FullTransactions, each [Time]>MaxTime),
  22. // Append the delta records
  23. out =Table.Combine({PrevTransactions,DeltaTransactions})
  24. in
  25. out
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement