======================================================
CSV:
let
Source = SharePoint.Files("https://site", [ApiVersion = 15]),
Step01 = Table.SelectRows(Source, each Text.StartsWith([Name], "Cadastro_Clientes_Textos" and [Extension] = ".CSV")),
Step03 = Table.SelectColumns(Step01, {"Content"}),
step04 = Table.AddColumn(Step03, "Cols", each Csv.Document(([Content]),[Delimiter=";", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])),
step05 = Table.RemoveColumns(step04, {"Content"}),
step06 = Table.ExpandTableColumn(step05, "Cols", {"Column1", "Column2"}, {"CUSTOMER", "Nome_Cliente"})
in
step06
======================================================
/*let
Source = SharePoint.Files("https://SHAREPOINT_XPTO/sites/SITE_XPTO", [ApiVersion = 15]),
Step1 = Table.SelectRows(Source, each Text.Contains([Folder Path], "Folder_XPTO") and [Name] = "FILE.xlsx"),
Step2 = Table.SelectColumns(Step1, {"Content"}),
Step3 = Table.AddColumn(Step2, "Cols", each Excel.Workbook([Content])),
*/
let
Source = SharePoint.Contents("https://SHAREPOINT_XPTO/SITE_XPTO", [ApiVersion = 15]),
Navigation = Source{[Name = "cdgdoc"]}[Content],
Step1 = Table.SelectRows(Navigation, each [Name] = "FILE_XPTO.xlsx"),
Step2 = Table.SelectColumns(Step1, {"Content"}),
Step3 = Table.AddColumn(Step2, "Cols", each Excel.Workbook([Content])),
Step4 = Table.RemoveColumns(Step3, {"Content"}),
Step5 = Table.ExpandTableColumn(Step4, "Cols", {"Name", "Data", "Item", "Kind", "Hidden"},
{"Name", "Data", "Item", "Kind", "Hidden"}),
XLSX (Professional):
let
Source = SharePoint.Files("https://site", [ApiVersion = 15]),
Step1 = Table.SelectRows(Source, each Text.Contains([Folder Path], "dataset_JoinWebservice") and [Name] = "DGEG_PostosJoin.xlsx"),
Step2 = Table.SelectColumns(Step1, {"Content"}),
Step3 = Table.AddColumn(Step2, "Cols", each Excel.Workbook([Content])),
Step4 = Table.RemoveColumns(Step3, {"Content"}),
Step5 = Table.ExpandTableColumn(Step4, "Cols", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
Step6 = Table.SelectRows(Step5, each [Name] = "Postos"),
Step7 = Table.RemoveColumns(Step6, {"Item", "Kind", "Hidden", "Name"}),
Step8 = Table.ExpandTableColumn(Step7, "Data", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
in
Step8
======================================================
XLSX(Personal/Sharepoint):
let
Source = SharePoint.Files("https://TENANT.sharepoint.com/personal/USERS_PT/", [ApiVersion = 15]),
Step1 = Table.SelectRows(Source, each ([Folder Path] = "https://TENANT.sharepoint.com/personal/USERS_PT/Documents/Desktop/APODesktop/datasets/")and Text.Contains([Name], "PostosSAP")),
Step2 = Table.SelectColumns(Step1, {"Content"}),
Step3 = Table.AddColumn(Step2, "Cols", each Excel.Workbook([Content])),
Step4 = Table.RemoveColumns(Step3, {"Content"}),
Step5 = Table.ExpandTableColumn(Step4, "Cols", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
Step6 = Table.SelectRows(Step5, each ([Kind] = "Sheet")),
Step7 = Table.SelectColumns(Step6,{"Data"}),
Step8 = Table.ExpandTableColumn(Step7 , "Data", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
in
Step8
======================================================
JSON:
let
Source = SharePoint.Files("https://Mysharepoint", [ApiVersion = 15]),
Step01 = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "https://Mysharepoint_SearchFolder/")),
Step02 = Table.SelectColumns(Step01, {"Content", "Name", "Extension", "Folder Path"}),
Step03 = Table.SelectRows(Step02, each [Extension] = ".json"),
Step04 = Table.SelectColumns(Step03, {"Content"}),
Step05 = Table.AddColumn(Step04, "Json", each Json.Document([Content], 65001)),
Step06 = Table.SelectColumns(Step05, {"Json"}),
Step07 = Table.ExpandListColumn(Step06, "Json")
in
Step07
======================================================
MySQL:
let
Source = MySQL.Database("timesheet.xxxxx.pt:3306", "DBFIN", [ReturnSingleDatabase=true]),
ssimulta_ssdbfin_bancos = Source{[Schema="dbfin",Item="bancos"]}[Data],
#"Renamed Columns" = Table.RenameColumns(ssimulta_ssdbfin_bancos,{{"Descritivo", "Banco"}})
in
#"Renamed Columns"
======================================================
Azure Datalake:
let
Source = AzureStorage.DataLake("https://DATALAKE.dfs.core.windows.net/FOLDER/SUBFOLDERS"),
Step01 = Table.SelectRows(Source, each ([Name] = "pXPTO.csv")),
Step03 = Table.SelectColumns(Step01, {"Content"}),
step04 = Table.AddColumn(Step03, "Cols", each Csv.Document(([Content]),[Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None])),
step05 = Table.RemoveColumns(step04, {"Content"}),
#"Expanded Cols" = Table.ExpandTableColumn(step05, "Cols", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Cols", [PromoteAllScalars=true])
in
#"Promoted Headers"
======================================================
Azure Synapse:
let
Source = Sql.Database("ResourceSynapse-ondemand.sql.azuresynapse.net", "DatalakeName",
[CommandTimeout = #duration(0, 2, 0, 0), Query = "SELECT *
FROM Vendas_Integradas.VIEW_XXXX
WHERE CALDAY >= 20220101 AND CALDAY <= 20221231"])
in
Source
======================================================
Azure Storage BLOB Content
Acesso via DATALAKE BLOB:
let
Source = AzureStorage.Blobs("https://DATALAKE.blob.core.windows.net/"),
Navigation = Source{[Name = "pbicontainer"]}[Data],
#"Filtered rows6" = Table.SelectRows(Navigation, each Text.StartsWith([Name], "FOLDER/")),
Step01 = Table.SelectRows(#"Filtered rows6", each [Name] = "FOLDER/FILE.CSV"),
Step03 = Table.SelectColumns(Step01, {"Content"}),
step04 = Table.AddColumn(Step03, "Cols", each Csv.Document(([Content]),[Delimiter=";", Columns=72, Encoding=1252, QuoteStyle=QuoteStyle.None])),
step06 = Table.RemoveColumns(step04, {"Content"}),
======================================================
Azure Storage DATALAKE Content
Acesso via DATALAKE:
let
Source = AzureStorage.DataLake("https://DATALAKE.dfs.core.windows.net/pbicontainer/FOLDER/FILE.CSV"),
Step03 = Table.SelectColumns(Source, {"Content"}),
step04 = Table.AddColumn(Step03, "Cols", each Csv.Document(([Content]),[Delimiter=";", Columns=72, Encoding=65001, QuoteStyle=QuoteStyle.None])),
step06 = Table.RemoveColumns(step04, {"Content"}),
======================================================
Azure Storage BLOB Parquet Content
let
Source = AzureStorage.Blobs("XPTO_DATALAKE"),
Step02 = Source{[Name = "XPTO_DATALAKE"]}[Data],
Step03 = Table.SelectRows(Step02, each Text.StartsWith([Name], "XPTO_PATH_Parquet/")),
Step04 = Table.SelectRows(Step03, each [Extension] = ".parquet"),
Step05 = Table.SelectColumns(Step04, {"Content"}),
Step06 = Table.AddColumn(Step05, "Cols", each Parquet.Document(([Content]))),
Step07 = Table.RemoveColumns(Step06, {"Content"}),
in
Step07
======================================================
Azure Storage BLOBContents Parquet
let
Source = Parquet.Document(
AzureStorage.BlobContents("https://DATALAKE_XPTO.blob.core.windows.net/CONTAINER_XPTO/PATH_XPTO/File.parquet"))
in
Source
======================================================
DATAFLOWS Old x New (PowerBI.Dataflows x PowerPlatform.Dataflows)
let
Source = PowerBI.Dataflows(),
workspaceId = Source{[workspaceId="WP_XPTO"]}[Data],
dataflowId = workspaceId{[dataflowId="DF_XPTO"]}[Data],
DIM_Table = dataflowId{[entity="DIM_XPTO"]}[Data],
in
DIM_Table
let
Source = PowerPlatform.Dataflows(),
Workspaces = Source{[Id="Workspaces"]}[Data],
Workspaces1 = Workspaces{[workspaceId="WP_XPTO"]}[Data],
DataflowId = Workspaces1{[dataflowId="DF_XPTO"]}[Data],
DIM_Table = DataflowId{[entity="DIM_XPTO",version=""]}[Data],
in
DIM_Table
No comments:
Post a Comment