power query - expanding new columns

Question

i have two table to merge and then i expand newcolumns. the problem is the first table can have new columns as we enter new data to a new month/year. the result does not include these new months.

is there a way to manage to expand new columns while merging ?

the queries :

let
    Source = #"320 Odemeler",
    #"Merged Queries" = Table.NestedJoin(Source,{"HESAP NO"},#"320 Faturalar",{"HESAP NO"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"HESAP NO", "HESAP ADI", "Nisan - 2016", "Haziran - 2016", "Temmuz - 2016", "Ağustos - 2016", "Eylül - 2016", "Ekim - 2016", "Kasım - 2016", "Aralık - 2016", "Ocak - 2017", "Şubat - 2017", "Mart - 2017", "Mayıs - 2017"}, {"HESAP NO.1", "HESAP ADI.1", "Nisan - 2016", "Haziran - 2016", "Temmuz - 2016", "Ağustos - 2016", "Eylül - 2016", "Ekim - 2016", "Kasım - 2016", "Aralık - 2016", "Ocak - 2017", "Şubat - 2017", "Mart - 2017", "Mayıs - 2017"})
in
    #"Expanded NewColumn"

the other query :

let
    Source = #"Tum Hareketler",
    #"Sorted Rows" = Table.Sort(Source,{{"DATE_", Order.Ascending}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "DATE_", "DATE_ - Copy"),
    #"Extracted Month Name" = Table.TransformColumns(#"Duplicated Column", {{"DATE_", each Date.MonthName(_, "tr-TR"), type text}}),
    #"Extracted Year" = Table.TransformColumns(#"Extracted Month Name",{{"DATE_ - Copy", Date.Year}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Year", "Tarih", each [DATE_] & " - " & Number.ToText([#"DATE_ - Copy"], "G","")),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"HESAP NO", "HESAP ADI", "Tarih"}, {{"Sum Alacak", each List.Sum([ALACAK]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Tarih]), "Tarih", "Sum Alacak", List.Sum)
in
    #"Pivoted Column"

maybe i can make a new list of the columns and set the "Table.ExpandTableColumn" of this list ? — Umut K
i created a new list named "Tarih" but i dont know the syntax to replace the part {"HESAP NO", "HESAP ADI", "Nisan - 2016", "Haziran - 2016", "Temmuz - 2016", "Ağustos - 2016", "Eylül - 2016", "Ekim - 2016", "Kasım - 2016", "Aralık - 2016", "Ocak - 2017", "Şubat - 2017", "Mart - 2017", "Mayıs - 2017"} as list... — Umut K
Source = Tarih and then what should i write into the Table.ExpandTableColumn ? — Umut K

Umut K Umut K · Accepted Answer · 2017-02-13T14:07:22

First I converted "Tarih" List to Table,

Then managed to write the below query :

let
    Liste = Tarih,
    TarihListe = Liste [Column1],
    Source = #"320 Odemeler",
    #"Merged Queries" = Table.NestedJoin(Source,{"HESAP NO"},#"320 Faturalar",{"HESAP NO"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", TarihListe , TarihListe )
in
    #"Expanded NewColumn"

So, every time a new month column added by the Pivot Column, I don't have to edit the query to add it to the query...

power query - expanding new columns

1 Answers