hi
Thank you for your answer.
I don't use Power BI.
Taking it out of the pivot table and reintroducing it into the process would cause me to do a lot of extra work, like a vicious cycle.
It would be better to do this during the power query before the pivot table result.
I think this is what your code does
"
Source = Excel.CurrentWorkbook(){[Name="inflation"]}[Content],
#"PivotColumnsResolved" = Table.UnpivotOtherColumns(Source, {}, "Property", "Value"),
"
But the next thing I need help with is:
It is the multiplication of the inflation coefficient under the period by all values corresponding to the same period.
let
rate = Excel.CurrentWorkbook(){[Name="inflation"]}[Content],
// Sembollerin alınması ve filtrelenmesi
tblSymbols = Excel.CurrentWorkbook(){[Name="tblSymbols_Sector"]}[Content],
RenamedColumns = Table.RenameColumns(tblSymbols, {{"Column1", "Symbol"}}),
FilteredSymbols = Table.SelectRows(RenamedColumns, each [Symbol] <> null),
// Sütun türlerinin değiştirilmesi
ChangedType = Table.TransformColumnTypes(FilteredSymbols, {{"Symbol", type text}}),
// Taban URL'lerin eklenmesi ve genişletilmesi
BaseURLs = Excel.CurrentWorkbook(){[Name="tblFinansal_URLs"]}[Content],
GetBaseURL = Table.AddColumn(ChangedType, "BasedURL", each BaseURLs),
ExpandedBasedURL = Table.ExpandTableColumn(GetBaseURL, "BasedURL", {"URL"}, {"URL"}),
// Sembollerin URL'ye eklenmesi ve verilerin alınması
ReplacedSymbol = Table.AddColumn(ExpandedBasedURL, "ReplaceSYMBOL", each Text.Replace([URL], "SYMBOL", [Symbol])),
GetSourceValue = Table.AddColumn(ReplacedSymbol, "GetData", each Table.FromList(Json.Document(Web.Contents([ReplaceSYMBOL]))[value], Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
// Verilerin genişletilmesi
ExpandedGetData = Table.ExpandTableColumn(GetSourceValue, "GetData", {"Column1"}, {"Column1"}),
ExpandedColumn = Table.ExpandRecordColumn(ExpandedGetData, "Column1", {"itemCode", "itemDescTr", "itemDescEng", "value1", "value2", "value3", "value4"}, {"itemCode", "itemDescTr", "itemDescEng", "value1", "value2", "value3", "value4"}),
// Seçilen sütunların unpivot edilmesi
UnpivotedColumns = Table.Unpivot(ExpandedColumn, {"value1", "value2", "value3", "value4"}, "ValueNo", "Value"),
// URL'den dönem bilgisi alınması
GetPdFromURL = Table.AddColumn(UnpivotedColumns, "GetPeriodFromURL", each Text.AfterDelimiter([URL], "&year"), type text),
SplitColumn = Table.SplitColumn(GetPdFromURL, "GetPeriodFromURL", Splitter.SplitTextByDelimiter("&year", QuoteStyle.None)),
// Dönem sütunlarının unpivot edilmesi
UnpivotedPeriodColumns = Table.Unpivot(SplitColumn, List.Select(Table.ColumnNames(SplitColumn), each Text.StartsWith(_, "GetPeriodFromURL.")), "Attribute", "TempPd"),
// Dönem bilgisine göre özel sütun eklenmesi
AddedCustom = Table.AddColumn(UnpivotedPeriodColumns, "Period", each if Text.End([ValueNo], 1) = Text.Start([TempPd], 1) then Text.Replace(Text.End(Text.Start([TempPd], Text.PositionOfAny([TempPd], {"&"}, Occurrence.First)), 4) & "/" & Text.End([TempPd], Text.Length([TempPd]) - Text.PositionOfAny([TempPd], {"="}, Occurrence.Last) - 1), "&", "") else null),
// Null değerlerin filtrelenmesi
FilteredRows = Table.SelectRows(AddedCustom, each ([Period] <> null) and ([TempPd] <> "")),
// Gereksiz sütunların kaldırılması
RemovedColumns = Table.RemoveColumns(FilteredRows, {"URL", "ReplaceSYMBOL", "ValueNo", "Attribute", "TempPd"}),
// Sütun türlerinin yeniden değiştirilmesi
ChangedTypeFinal = Table.TransformColumnTypes(RemovedColumns, {{"Value", Int64.Type}}),
// Pivot işlemi
PivotedColumn = Table.Pivot(ChangedTypeFinal, List.Distinct(ChangedTypeFinal[Period]), "Period", "Value")
in
PivotedColumn
DNZ.xlsx
