Hi,
I've pbix report file (project file) which was connected to Excel Sheets before. Now I wanted to change the DataSource to SharePoint Folder where the same Excel files are available. How to acheive this?
Using Advance Eiditor how to achive this just by replacing the Source data?
My Excel Data source code given below
let
Source = Excel.Workbook(Web.Contents("https://healthineersapc.sharepoint.com/teams/iMS4CGlobal/IMS4C%20Global%20Data/On%20Time%20Completio..."), null, true),
CM_Sheet = Source{[Item="CM",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(CM_Sheet, [PromoteAllScalars=true]),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Promoted Headers", {{"Zone", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Zone"),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter1", each true),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"overdue", "Assigned to", "ChangeTitle", "Change Description", "CreatedDAY", "CreatedMONTH", "CreatedMonthName", "Quarter", "CreatedYEAR", "Created By", "CAPA ID", "Process Owner", "assigned for Validation", "ArchiveDate", "Path", "OD", "FirstReminder", "CalculatedCounter", "DateCreated", "CurrentDate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"CreatedDate", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Zone] <> " Global / iMS4C" and [Zone] <> "Global / iMS4C")),
#"Trimmed Text" = Table.TransformColumns(#"Filtered Rows1",{{"Zone", Text.Trim, type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Trimmed Text",{"_Created"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "Refline 50%", each "0.5"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Refline 50%", Percentage.Type}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Refline 50%"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"Target Date", type date}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type2", each [Target Date] <> null and [Target Date] <> "")
in
#"Filtered Rows2"
My Sharepoint Folder Code given below.
let
Source = SharePoint.Files("https://healthineersapc.sharepoint.com/teams/iMS4CGlobal", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://healthineersapc.sharepoint.com/teams/iMS4CGlobal/IMS4C Global Data/On Time Completion of Changes(CM) KPI/Region/")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"overdue", type text}, {"Cat", type text}, {"Change ID", type text}, {"Status", type text}, {"Assigned to", type text}, {"ChangeTitle", type text}, {"Change Description", type text}, {"Target Date", type date}, {"Created", type text}, {"CreatedDate", type date}, {"CreatedDAY", Int64.Type}, {"CreatedMONTH", Int64.Type}, {"CreatedMonthName", type text}, {"Quarter", type text}, {"CreatedYEAR", Int64.Type}, {"Created By", type text}, {"CAPA", type logical}, {"CAPA ID", type text}, {"Process Owner", type text}, {"assigned for Validation", type text}, {"ArchiveDate", type any}, {"Zone", type text}, {"Item Type", type text}, {"Path", type text}, {"OD", type any}, {"FirstReminder", type any}, {"CalculatedCounter", type any}, {"DateCreated", type any}, {"_Created", type datetime}, {"CM Status", type text}, {"CurrentDate", type datetime}, {"Overdue (days)", type any}, {"Overdue / Not overdue", type text}})
in
#"Changed Type"
Now how to replace the above Data Source code with the Original Excel DataSource code?
It's great help, if someone help me on this. Thanks!
