@Roberto I am not sure what columns names are supposed to be so I leave that to you:
funds.pbix (117.2 KB)
Now you can create dimensions out of it on your own.
let
Source = Json.Document ( Web.Contents ( "https://api.mfapi.in/mf/144838" ) ),
data = Source[data],
ConvertedtoTable = Table.FromList (
data,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
ExpandedColumn1 = Table.ExpandRecordColumn (
ConvertedtoTable,
"Column1",
{ "date", "nav" },
{ "Column1.date", "Column1.nav" }
),
RenamedColumns = Table.RenameColumns (
ExpandedColumn1,
{ { "Column1.date", "Date" }, { "Column1.nav", "Value" } }
),
FundInfoList = List.Transform ( Record.ToList ( Source[meta] ), ( row ) => Text.From ( row ) ),
AddedCustom = Table.AddColumn (
RenamedColumns,
"Custom",
( CurrentRow ) =>
let
ToRecord = Record.ToList ( CurrentRow ),
InsertFundInfo = List.InsertRange ( ToRecord, 0, FundInfoList ),
ToTable = Table.FromList ( InsertFundInfo ),
TransposeTable = Table.Transpose ( ToTable )
in
TransposeTable
),
RemovedOtherColumns = Table.SelectColumns ( AddedCustom, { "Custom" } ),
ExpandedCustom = Table.ExpandTableColumn (
RemovedOtherColumns,
"Custom",
{ "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7" },
{ "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7" }
),
ChangedType = Table.TransformColumnTypes (
ExpandedCustom,
{
{ "Column1", type text },
{ "Column2", type text },
{ "Column3", type text },
{ "Column4", Int64.Type },
{ "Column5", type text },
{ "Column6", type date },
{ "Column7", type number }
}
)
in
ChangedType
