Forum Discussion

samnaw's avatar
samnaw
Resolver I
6 years ago

Disconnect source but keep the table

Hello:

 

I have an issue where I have a xls sheet that I am connecting to. But this xls is on my desktop and I dont want it connected. I just want a table in PBI that is stand alone. 

 

Power Query

 

let
Source = Excel.Workbook(File.Contents("\\abc\abc\Desktop\Copy of Acc.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Cust_PrefStore", type text}, {"Nbr of New Loyalty Customers", Int64.Type}})
in
#"Changed Type"

 

------------------------------------------------------------------------------------

 

I dont want it linked. ("\\abc\abc\Desktop\Copy of Acc.xlsx"). Is this possible

 

 

Need Help
Tips and Tricks

2 Replies

  • michaelx2's avatar
    michaelx2
    Microsoft Employee
    6 years ago

    Hello samnaw 

    You may use 'Enter data' to create a table, copy the content from excel and paste the content to the table. 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("{encrypted string}", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, StartTime = _t, EndTime = _t, Labor = _t, Note = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"StartTime", type datetime}, {"EndTime", type datetime}, {"Labor", type number}, {"Note", type text}})
in
    #"Changed Type"

     

    Best Regards

  • Mycall65's avatar
    Mycall65
    Frequent Visitor
    1 year ago

    That won't work unless you have fewer than 3000 cells to upload