I wish I knew all of this or could find one place to learn everything about Web.Contents power query function. I used it in 100 different places and fell in love with the Web.Contents power query function. I have combined all the different parameters used and different API call examples. I am going to share them all with you and this is the first post regarding that. I am going to post one param use example daily. So please follow.
To follow along, you will need these helper functions that I have created to simplify the code. It will be used in most of the examples in this series.
fxJsonBinaryToTable.pq code:
(
ResponseJsonBinary as binary,
optional IsAddColumnNamePrefix as nullable logical,
optional ColumnNameSeperator as nullable text
) as table =>
let
// FilePath = "C:\Users\USER\Desktop\response.json",
// ResponseJsonBinary = File.Contents(FilePath),
// IsAddColumnNamePrefix = true,
// ColumnNameSeperator = ".",
NeedToAddColumnNamePrefix =
if IsAddColumnNamePrefix is null then
true
else
IsAddColumnNamePrefix,
Seperator = if ColumnNameSeperator is null then "." else ColumnNameSeperator,
DataFromJSON = Json.Document(ResponseJsonBinary),
DataType = Value.Type(DataFromJSON),
ConvertedToTable =
if DataType = List.Type then
Table.FromList(DataFromJSON, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
else
Table.PromoteHeaders(Table.Transpose(Record.ToTable(DataFromJSON))),
ExpandedRecursively = try
fxExpandColumnsRecursively(ConvertedToTable)
otherwise
fxExpandColumnsRecursively(ConvertedToTable, NeedToAddColumnNamePrefix, Seperator)
in
ExpandedRecursively
fxExpandColumnsRecursively.pq Code
(
InputTable as table,
optional IsAddColumnNamePrefix as nullable logical,
optional ColumnNameSeperator as nullable text
) as table =>
let
// InputTable =fxCleanData ,
// IsAddColumnNamePrefix = true,
// ColumnNameSeperator = "|",
IsExpandable = fxIsAnyColumnExpandAble(InputTable),
ExpandRecursively =
if IsExpandable then
List.Generate(
() => [
Result = fxExpandOnce(InputTable, IsAddColumnNamePrefix, ColumnNameSeperator),
IsExpandAgain = true
],
each _[IsExpandAgain],
each [
Result = fxExpandOnce([Result], IsAddColumnNamePrefix, ColumnNameSeperator),
IsExpandAgain = fxIsAnyColumnExpandAble([Result])
],
each _[Result]
)
else
{InputTable},
FinalTable = List.Last(ExpandRecursively)
in
FinalTable
fxExpandOnce.pq Code
(
InputTable as table,
optional IsAddColumnNamePrefix as nullable logical,
optional ColumnNameSeperator as nullable text
) as table =>
let
// InputTable = fxCleanData,
ColumnNames = Table.ColumnNames(InputTable),
ExpandedTable = List.Generate(
() => [
Result = fxExpandColumnDynamically(
InputTable,
ColumnNames{Index},
IsAddColumnNamePrefix,
ColumnNameSeperator
),
Index = 0
],
each _[Index] < List.Count(ColumnNames),
each [
Result = fxExpandColumnDynamically(
[Result],
ColumnNames{Index},
IsAddColumnNamePrefix,
ColumnNameSeperator
),
Index = [Index] + 1
],
each _[Result]
),
FinalTable = List.Last(ExpandedTable)
in
FinalTable
fxExpandColumnDynamically.pq Code
(
InputTable as table,
ColumnName as text,
optional IsAddColumnNamePrefix as nullable logical,
optional ColumnNameSeperator as nullable text
) as table =>
let
// InputTable = #"fxExpandColumnsRecursively (2)",
// ColumnName = "savingsPlan",
// IsAddColumnNamePrefix = true,
// ColumnNameSeperator = "|",
IsAddColumnNamePrefixCorrected =
if IsAddColumnNamePrefix = null then
false
else
IsAddColumnNamePrefix,
ColumnNameSeperatorCorrected = if ColumnNameSeperator = null then "|" else ColumnNameSeperator,
ColumnValuesAsList = List.Transform(
Table.ToRows(Table.SelectColumns(InputTable, ColumnName)),
each _{0}
),
IsRecordFields = List.MatchesAny(ColumnValuesAsList, each Value.Is(_, Record.Type)),
IsListFields = List.MatchesAny(ColumnValuesAsList, each Value.Is(_, List.Type)),
IsTableFields = List.MatchesAny(ColumnValuesAsList, each Value.Is(_, Table.Type)),
FieldNames =
if IsRecordFields then
List.Union(
List.Transform(ColumnValuesAsList, each if _ = null then {} else Record.FieldNames(_))
)
else if IsTableFields then
List.Union(
List.Transform(ColumnValuesAsList, each if _ = null then {} else Table.ColumnNames(_))
)
else
{},
AddColumnNamePrefix =
if IsAddColumnNamePrefixCorrected then
List.Transform(FieldNames, each ColumnName & ColumnNameSeperatorCorrected & _)
else
FieldNames,
ExpandedTable =
if IsRecordFields then
Table.ExpandRecordColumn(InputTable, ColumnName, FieldNames, AddColumnNamePrefix)
else if IsListFields then
Table.ExpandListColumn(InputTable, ColumnName)
else if IsTableFields then
Table.ExpandTableColumn(InputTable, ColumnName, FieldNames, AddColumnNamePrefix)
else
InputTable
in
ExpandedTable
fxIsAnyColumnExpandAble.pq Code
(InputTable as table) as logical =>
let
// InputTable = fxCleanData,
IsAnyColumnExpandAble = List.MatchesAny(
Table.ColumnNames(InputTable),
each fxIsColumnExpandAble(InputTable, _)
)
in
IsAnyColumnExpandAble
fxIsColumnExpandAble.pq Code
(InputTable as table, ColumnName as text) as logical =>
let
// InputTable = fxCleanData,
// ColumnName = "Column1",
ColumnValuesAsList = List.Transform(
Table.ToRows(Table.SelectColumns(InputTable, ColumnName)),
each _{0}
),
IsRecordFields = List.MatchesAny(ColumnValuesAsList, each Value.Is(_, Record.Type)),
IsListFields = List.MatchesAny(ColumnValuesAsList, each Value.Is(_, List.Type)),
IsTableFields = List.MatchesAny(ColumnValuesAsList, each Value.Is(_, Table.Type)),
IsExpandable = IsTableFields or IsListFields or IsRecordFields
in
IsExpandable
1 Comment.