Taming Arbitrary Data in Power BI

Taming Arbitrary Data in Power BI

Have you ever had to prepare a proof of concept for a BI system or report, having nothing more than a group of aesthetically pleasing user-created excel files

Then you will know how getting all that non-machine-friendly data into a database can consume so much time. Time you could use instead invest in preparing useful charts to show better insight into the business.

One of the very useful things about Power BI is how it lets you transform irregular data on-the-spot, without a need for prior complex treatment on another tool or system. This means you can take a jumble of assorted data and turn it into a usable model quick - an invaluable feature for the conceptualization phase, or even for the delivery phase, when you have no control over the format of your data feeds.

In this article you will see an example of how to do this in Power BI.

read more

How To Find And Remove Old Files In PowerShell

How To Find And Remove Old Files In PowerShell

This article explains how to list and remove old files in a folder using PowerShell.

Question

So I have all these files in my backup folder. How can I keep only the files modified at most seven days ago, whilst removing the rest?

Short Answer

We can use a simple chain like this:

Get-ChildItem -Recurse |

    # change the extension as appropriate
    ?{ $_.Extension -eq ".bak" } |

    # change the time window as appropriate
    ?{ $_.LastWriteTime -lt (Get-Date).AddDays(-7) } |

    # now remove the results
    Remove-Item

We can then add or remove filters to this pattern as appropriate to your scenario.

So what more stuff can we add?

Long Answer

To see a list of all filters, we can pipe a file object to Get-Member.

Get-ChildItem | Select-Object -First 1 | Get-Member

It doesn’t matter where or on what files we run this. The result will be the same list of file properties, which we can see below:

   TypeName: System.IO.DirectoryInfo

Name                      MemberType     Definition                                                                         
----                      ----------     ----------                                                                         
Mode                      CodeProperty   System.String Mode{get=Mode;}                                                      
Create                    Method         void Create(), void Create(System.Security.AccessControl.DirectorySecurity direc...
CreateObjRef              Method         System.Runtime.Remoting.ObjRef CreateObjRef(type requestedType)                    
CreateSubdirectory        Method         System.IO.DirectoryInfo CreateSubdirectory(string path), System.IO.DirectoryInfo...
Delete                    Method         void Delete(), void Delete(bool recursive)                                         
EnumerateDirectories      Method         System.Collections.Generic.IEnumerable[System.IO.DirectoryInfo] EnumerateDirecto...
EnumerateFiles            Method         System.Collections.Generic.IEnumerable[System.IO.FileInfo] EnumerateFiles(), Sys...
EnumerateFileSystemInfos  Method         System.Collections.Generic.IEnumerable[System.IO.FileSystemInfo] EnumerateFileSy...
Equals                    Method         bool Equals(System.Object obj)                                                     
GetAccessControl          Method         System.Security.AccessControl.DirectorySecurity GetAccessControl(), System.Secur...
GetDirectories            Method         System.IO.DirectoryInfo[] GetDirectories(), System.IO.DirectoryInfo[] GetDirecto...
GetFiles                  Method         System.IO.FileInfo[] GetFiles(string searchPattern), System.IO.FileInfo[] GetFil...
GetFileSystemInfos        Method         System.IO.FileSystemInfo[] GetFileSystemInfos(string searchPattern), System.IO.F...
GetHashCode               Method         int GetHashCode()                                                                  
GetLifetimeService        Method         System.Object GetLifetimeService()                                                 
GetObjectData             Method         void GetObjectData(System.Runtime.Serialization.SerializationInfo info, System.R...
GetType                   Method         type GetType()                                                                     
InitializeLifetimeService Method         System.Object InitializeLifetimeService()                                          
MoveTo                    Method         void MoveTo(string destDirName)                                                    
Refresh                   Method         void Refresh()                                                                     
SetAccessControl          Method         void SetAccessControl(System.Security.AccessControl.DirectorySecurity directoryS...
ToString                  Method         string ToString()                                                                  
PSChildName               NoteProperty   System.String PSChildName=folder                                                   
PSDrive                   NoteProperty   System.Management.Automation.PSDriveInfo PSDrive=C                                 
PSIsContainer             NoteProperty   System.Boolean PSIsContainer=True                                                  
PSParentPath              NoteProperty   System.String PSParentPath=Microsoft.PowerShell.CoreFileSystem::C:WorkTest Files
PSPath                    NoteProperty   System.String PSPath=Microsoft.PowerShell.CoreFileSystem::C:WorkTest Filesfo...
PSProvider                NoteProperty   System.Management.Automation.ProviderInfo PSProvider=Microsoft.PowerShell.CoreF...
Attributes                Property       System.IO.FileAttributes Attributes {get;set;}                                     
CreationTime              Property       datetime CreationTime {get;set;}                                                   
CreationTimeUtc           Property       datetime CreationTimeUtc {get;set;}                                                
Exists                    Property       bool Exists {get;}                                                                 
Extension                 Property       string Extension {get;}                                                            
FullName                  Property       string FullName {get;}                                                             
LastAccessTime            Property       datetime LastAccessTime {get;set;}                                                 
LastAccessTimeUtc         Property       datetime LastAccessTimeUtc {get;set;}                                              
LastWriteTime             Property       datetime LastWriteTime {get;set;}                                                  
LastWriteTimeUtc          Property       datetime LastWriteTimeUtc {get;set;}                                               
Name                      Property       string Name {get;}                                                                 
Parent                    Property       System.IO.DirectoryInfo Parent {get;}                                              
Root                      Property       System.IO.DirectoryInfo Root {get;}                                                
BaseName                  ScriptProperty System.Object BaseName {get=$this.Name;}      

Do you notice there is also a CreationTime property in there? We can make use of this property to remove files that changed more than a week ago, yet keep files that were created less than a week ago.

This sounds confusing and illogical, doesn’t it? How can a file have been created after it was changed? It doesn’t make sense.

Yet this is common in Windows as it happens all the time we copy a file from one place to another. When a file is copied in Windows, its CreationTime is set as the date the copy occured, while its LastWriteTime is inherited from the original copy. This makes the file appear like it was created after it was modified though, of course, it wasn’t.

In the previous example, if we wanted to take this into consideration and avoid removing newly copied files, we could simply add another criterion:

Get-ChildItem -Recurse |

    # change the extension as appropriate
    ?{ $_.Extension -eq ".csv" } |

    # change the modified time window as appropriate
    ?{ $_.LastWriteTime -lt (Get-Date).AddDays(-7) } |

    # change the created time window as appropriate
    ?{ $_.CreationTime -lt (Get-Date).Add(-7) } |

    Remove-Item

And that’s it. Easy, isn’t it?

read more