Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- let
- Source = Excel.Workbook(File.Contents("D:\Downloads\FactTransactions.xlsx"), null, true),
- Transactions_Table = Source{[Item="Transactions",Kind="Table"]}[Data],
- FullTransactions = Table.TransformColumnTypes(Transactions_Table,{{"Time", type datetime}, {"Value", Int64.Type}}),
- // Meta Query
- // Function that gets the port number of the $Embedded$ tabular model
- 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}),
- // Get $Embedded$ port number
- Port = Table.FromList(MSMDSRVport(),null,{"port"}){0},
- // Get the database/catalog name
- Catalog = AnalysisServices.Databases("localhost:"&Port[port])[Name]{0},
- // Get the contents of QueryLog table from tabular model
- PrevTransactionsRaw = AnalysisServices.Database("localhost:"&Port[port],Catalog,[Query="EVALUATE (Transactions)"]),
- // Clean the column names to match the table in Power Query
- PrevTransactions = Table.RenameColumns(PrevTransactionsRaw ,{{"Transactions[Time]","Time"},{"Transactions[Value]","Value"}}),
- // Find the latest Timestamp
- MaxTime = Table.Last(PrevTransactions)[Time],
- // Select Delta records
- DeltaTransactions = Table.SelectRows(FullTransactions, each [Time]>MaxTime),
- // Append the delta records
- out =Table.Combine({PrevTransactions,DeltaTransactions})
- in
- out
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement