let
fnParentValuesRecursive = (src as table, ParentField as text, ChildField as text, ValueField as text) => let
Inital = Table.FromColumns({Table.Column(src,ChildField)},{"child"}),
fn = (optional table as nullable table, optional depth as nullable number) =>
let
t = if table = null then Inital else table,
d = if depth = null then 1 else depth,
AddedColumn = Table.AddColumn(t, "x", each
let
Child = [child],
Filter = Table.SelectRows(src,(r)=>Record.Field(r,ChildField)=Child),
Select = Table.SelectColumns(Filter,{ValueField,ParentField}),
Renamed = Table.RenameColumns(Select,{{ValueField,"val"&Text.From(d)},{ParentField,"child"}}),
Dummy = #table({"child","x"},{})
in if Table.RowCount(Filter)>0 then @fn(Renamed,d+1) else Dummy),
DistinctColumns = List.Distinct(List.Combine(List.Transform(AddedColumn[x],Table.ColumnNames))),
AllColumns = if d>1 then Table.ColumnNames(AddedColumn[x]{0}) else DistinctColumns,
NeededColumns = List.Select(AllColumns,each Text.StartsWith(_,"val")),
Expand = Table.ExpandTableColumn(AddedColumn,"x",NeededColumns),
Combine = Table.CombineColumns(Expand,List.Reverse(NeededColumns),(a)=>Text.TrimStart(Text.Combine(List.RemoveNulls(a),"|"),{"|"}),"val")
in Combine
in Table.RenameColumns(fn(),{{"child",ChildField},{"val",ValueField}}),
Source = Text.Replace(Text.Replace(Text.Replace(Text.FromBinary(File.Contents("C:\Users\Krosav4ig\Downloads\XML файл.xml")),"#(cr,lf)","\n"),"<","<"),">",">"),
DataSet = Xml.Tables(Web.Page("<script>
document.write('<table><tr><th>xml</th></tr><tr><td>"&Source&"'
.replace(/(\<[frg])(\d+)/g,'$1 n=""$2""')
.replace(/(\<\/*[frg])\d+(\>)/g,'$1$2')+'</td></tr></table>')
</script>"){0}[Data]{0}[xml]){1}[Table],
Date = Date.From(Table.SelectRows(DataSet,each [Fields]{0}[f]{0}[#"Attribute:Id"]="_Rpt_Period_Object")[Records]{0}[r]{0}[#"Attribute:f21"]{0}),
Groups = let
a = Table.Combine(List.Transform(List.RemoveNulls(DataSet[Groups]),each _{0}[g]))[[DataSet],[#"Attribute:pKey"]],
b = Table.ReplaceValue(a,null,null,(a,b,c)=>
let
rec=a{0}[Records]{0}[r]
in
if a{0}[Fields]{0}[f]{[#"Attribute:Id"="_GrpTreePerent_"]}?=null
then rec
else fnParentValuesRecursive(Table.Buffer(rec),"Attribute:f2","Attribute:f0","Attribute:f1")
,{"DataSet"}),
c = Table.ExpandTableColumn(Table.NestedJoin(b,"Attribute:pKey",DataSet{[#"Attribute:Id"="300"]}[Fields]{0}[f],"Attribute:Id","a"),"a",{"Attribute:n"}),
d = Table.ExpandTableColumn(c, "DataSet", {"Attribute:f0", "Attribute:f1"})
in d,
Records = Table.SelectColumns(DataSet{[#"Attribute:Id"="300"]}[Records]{0}[r],List.Transform({1,2,9,18,19,34},each "Attribute:f"& Text.From(_))),
Unpivot = Table.Unpivot(Records, {"Attribute:f18", "Attribute:f19"}, "Атрибут", "Значение"),
Replace = Table.ReplaceValue(Unpivot,"Attribute:f","",Replacer.ReplaceText,{"Атрибут"}),
Nested = Table.NestedJoin(Replace,{"Атрибут","Значение"},Groups,{"Attribute:n","Attribute:f0"}," "),
Expand = Table.ExpandTableColumn(Table.RemoveColumns(Nested,{"Значение"}), " ", {"Attribute:f1"}, {"Значение"}),
Pivot = Table.Pivot(Expand, List.Distinct(Expand[Атрибут]), "Атрибут", "Значение"),
Merge = Table.CombineColumns(Pivot,{"Attribute:f1", "Attribute:f2", "Attribute:f9"},(a)=>
[ Количество=Number.From(a{2}),
Закупка=Number.From(a{0})/10000,
#"Сумма закупки"=Количество*Закупка,
Продажа=Number.From(a{1})/10000,
#"Сумма продажи"=Количество*Продажа,
Дата=Date ]
,"x"),
Expand1 = Table.ExpandRecordColumn(Merge, "x", {"Количество", "Закупка", "Сумма закупки", "Продажа", "Сумма продажи","Дата"}),
Renamed = Table.RenameColumns(Expand1,{{"Attribute:f34", "Товар"}, {"18", "Место реализации"}, {"19", "Товарная группа"}})
in
Renamed |
