Forum Discussion

arock-well's avatar
arock-well
Responsive Resident
3 years ago

Getting SSRS report data using a Web URL

We have third party software that uses an on-prem SQL Server database. Trying to understand all the data we need to create a Power BI report has proven to be complicated to figure out what all they'r...
Need Help
Tips and Tricks
JSpe's avatar
JSpe
New Member
2 years ago

Here is the easiest way to get your SSRS URL (often found as an AtomSVC file) as a dataset in PBi:  

 

 

let
    // Basis: 
    OpenAtomSVCFile = (atomsvc_Contents as text) =>
        let
            Source = Xml.Tables(atomsvc_Contents),
            Table0 = Source{0}[Table],
            Table = Table0{1}[Table],
            #"Changed Type" = Table.TransformColumnTypes(Table, {{"Attribute:href", type text}}),
            #"Attribute:href" = #"Changed Type"{0}[#"Attribute:href"],
            #"Clean Ampersands" = Text.Replace(#"Attribute:href", "amp;", ""),
            #"Change to CSV Format" = Text.Replace(#"Clean Ampersands", "Format=ATOM", "Format=csv"),
            Request = Csv.Document(Web.Contents(#"Change to CSV Format"), [Delimiter=","]),
            
            #"Promoted Headers" = Table.PromoteHeaders(#"Request", [PromoteAllScalars=true])
        in
            #"Promoted Headers"
in
    OpenAtomSVCFile