none
Transform dynamic week numbers to fixed week numbers RRS feed

  • Question

  • Hi all

    I'm new to PowerQuery and trying to find a solution for following issue.

    This first table is my source. Every time i get an update, the week numbers will be different.

    What I'm trying to get to is this result:

    I've already been trying some options found on this forum, but wasn't able to find a good way yet.

    Would any of you be able to help?

    Thanks

    Bart

    Friday, December 29, 2017 3:24 PM

Answers

  • That's not the easiest task for a beginner, so I've uploaded a sample here: https://1drv.ms/x/s!Av_aAl3fXRbehbEtoWQtSq-q7O8nlg

    Code is this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        SortWeek = Table.AddColumn(Source, "SortWeek", each Number.From(Text.AfterDelimiter([Week], ".")&Text.BeforeDelimiter([Week], "."))),
        Weeks = List.Sort(List.Distinct(SortWeek[SortWeek])),
        #"Converted to Table" = Table.FromList(Weeks, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        WeekIndices = Table.AddIndexColumn(#"Converted to Table", "Week", 1, 1),
        #"Removed Columns" = Table.RemoveColumns(SortWeek,{"Week"}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Material", "SortWeek"}, "Attribute", "Value.1"),
        LookupWeekTranslation = Table.NestedJoin(#"Unpivoted Columns",{"SortWeek"},WeekIndices,{"Column1"},"Unpivoted Columns",JoinKind.LeftOuter),
        #"Expanded Unpivoted Columns" = Table.ExpandTableColumn(LookupWeekTranslation, "Unpivoted Columns", {"Week"}, {"Week"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Expanded Unpivoted Columns",{"SortWeek"}),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns1", {{"Week", type text}}, "en-US"),{"Week", "Attribute"},Combiner.CombineTextByDelimiter(" Week ", QuoteStyle.None),"Week"),
        #"Sorted Rows" = Table.Sort(#"Merged Columns",{{"Week", Order.Ascending}}),
        #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Week]), "Week", "Value.1")
    in
        #"Pivoted Column"

    You have to number your weeks and create a lookup-table from it and then perform a multi-column-pivot. Please check out this article about details of this method: http://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/  

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Friday, December 29, 2017 8:29 PM
    Moderator
  • "You have to number your weeks and create a lookup-table from it and then perform a multi-column-pivot."

    Hi Imke, If we can assume the week numbering as you suggest, then another approach would be the following, with exact column naming and order as requested (where week value precedes week quantity):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        TransformedColumn1 = Table.TransformColumns(Source, {"Week", each Text.End(_, 4) & Text.Start(_, 2)}),
        TransformedColumn2 = Table.TransformColumns(TransformedColumn1, {"Week", each List.PositionOf(List.Sort(List.Distinct(TransformedColumn1[Week])), _) + 1}),
        AddedCustom1 = Table.AddColumn(TransformedColumn2, "Week Name", each "Week " & Text.From([Week])),
        RemovedColumn = Table.RemoveColumns(AddedCustom1,{"Week"}),
        UnpivotedColumns = Table.UnpivotOtherColumns(RemovedColumn, {"Material", "Week Name"}, "Attribute", "Value.1"),
        AddedCustom2 = Table.AddColumn(UnpivotedColumns, "Custom", each [Week Name] & " - " & [Attribute]),
        RemovedColumns = Table.RemoveColumns(AddedCustom2,{"Week Name", "Attribute"}),
        PivotedColumn = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[Custom]), "Custom", "Value.1"),
        ReorderedColumnsList = List.Sort(List.Skip(Table.ColumnNames(PivotedColumn)), each if Text.Contains(_, "quantity") then Text.Replace(_, "quantity", "2") else Text.Replace(_, "value", "1")),
        ReorderedColumns = Table.ReorderColumns(PivotedColumn, ReorderedColumnsList)
    in
        ReorderedColumns


    Friday, December 29, 2017 11:55 PM
  • My 2 cents: a solution created using only standard menu options.

    3 Queries:

    Table1 imports Table1 and splits the Week column.

    FixedWeek references Table1 and creates fixed week numbers.

    Result references Table1, unpivots, merges with FixedWeek, sorts, merges columns and pivots.

    This is how it is created:
    https://youtu.be/Ngb9mXn3rI0

    These are the resulting queries:

    Table1:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"Week", type number}, {"value", Int64.Type}, {"quantity", Int64.Type}}),
        #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Week", type text}}, "en-US"), "Week", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Week.1", "Week.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Week.1", Int64.Type}, {"Week.2", Int64.Type}})
    in
        #"Changed Type1"

    FixedWeek:

    let
        Source = Table1,
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Week.1", "Week.2"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
        #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Week.2", Order.Ascending}, {"Week.1", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
        #"Added Prefix" = Table.TransformColumns(#"Added Index", {{"Index", each "Week " & Text.From(_, "en-US"), type text}})
    in
        #"Added Prefix"

    Result:

    let
        Source = Table1,
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Material", "Week.1", "Week.2"}, "Attribute", "Value.1"),
        #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns",{"Week.1", "Week.2"},FixedWeek,{"Week.1", "Week.2"},"FixedWeek",JoinKind.LeftOuter),
        #"Expanded FixedWeek" = Table.ExpandTableColumn(#"Merged Queries", "FixedWeek", {"Index"}, {"Index"}),
        #"Sorted Rows" = Table.Sort(#"Expanded FixedWeek",{{"Index", Order.Ascending}, {"Attribute", Order.Descending}}),
        #"Merged Columns" = Table.CombineColumns(#"Sorted Rows",{"Index", "Attribute"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged"),
        #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Week.1", "Week.2"}),
        #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value.1")
    in
        #"Pivoted Column"



    • Edited by MarcelBeug Saturday, December 30, 2017 9:10 AM
    • Marked as answer by aikibart Tuesday, January 9, 2018 9:11 PM
    Saturday, December 30, 2017 9:08 AM

All replies

  • It's unclear what logic is used to name the week numbers after transformation. Is there some offset based on the actual week numbers in column 2? E.g., after transformation, week numbers 51, 1, and 2 for ABC are 2, 3, and 4.
    Friday, December 29, 2017 8:19 PM
  • That's not the easiest task for a beginner, so I've uploaded a sample here: https://1drv.ms/x/s!Av_aAl3fXRbehbEtoWQtSq-q7O8nlg

    Code is this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        SortWeek = Table.AddColumn(Source, "SortWeek", each Number.From(Text.AfterDelimiter([Week], ".")&Text.BeforeDelimiter([Week], "."))),
        Weeks = List.Sort(List.Distinct(SortWeek[SortWeek])),
        #"Converted to Table" = Table.FromList(Weeks, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        WeekIndices = Table.AddIndexColumn(#"Converted to Table", "Week", 1, 1),
        #"Removed Columns" = Table.RemoveColumns(SortWeek,{"Week"}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Material", "SortWeek"}, "Attribute", "Value.1"),
        LookupWeekTranslation = Table.NestedJoin(#"Unpivoted Columns",{"SortWeek"},WeekIndices,{"Column1"},"Unpivoted Columns",JoinKind.LeftOuter),
        #"Expanded Unpivoted Columns" = Table.ExpandTableColumn(LookupWeekTranslation, "Unpivoted Columns", {"Week"}, {"Week"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Expanded Unpivoted Columns",{"SortWeek"}),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns1", {{"Week", type text}}, "en-US"),{"Week", "Attribute"},Combiner.CombineTextByDelimiter(" Week ", QuoteStyle.None),"Week"),
        #"Sorted Rows" = Table.Sort(#"Merged Columns",{{"Week", Order.Ascending}}),
        #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Week]), "Week", "Value.1")
    in
        #"Pivoted Column"

    You have to number your weeks and create a lookup-table from it and then perform a multi-column-pivot. Please check out this article about details of this method: http://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/  

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Friday, December 29, 2017 8:29 PM
    Moderator
  • "You have to number your weeks and create a lookup-table from it and then perform a multi-column-pivot."

    Hi Imke, If we can assume the week numbering as you suggest, then another approach would be the following, with exact column naming and order as requested (where week value precedes week quantity):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        TransformedColumn1 = Table.TransformColumns(Source, {"Week", each Text.End(_, 4) & Text.Start(_, 2)}),
        TransformedColumn2 = Table.TransformColumns(TransformedColumn1, {"Week", each List.PositionOf(List.Sort(List.Distinct(TransformedColumn1[Week])), _) + 1}),
        AddedCustom1 = Table.AddColumn(TransformedColumn2, "Week Name", each "Week " & Text.From([Week])),
        RemovedColumn = Table.RemoveColumns(AddedCustom1,{"Week"}),
        UnpivotedColumns = Table.UnpivotOtherColumns(RemovedColumn, {"Material", "Week Name"}, "Attribute", "Value.1"),
        AddedCustom2 = Table.AddColumn(UnpivotedColumns, "Custom", each [Week Name] & " - " & [Attribute]),
        RemovedColumns = Table.RemoveColumns(AddedCustom2,{"Week Name", "Attribute"}),
        PivotedColumn = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[Custom]), "Custom", "Value.1"),
        ReorderedColumnsList = List.Sort(List.Skip(Table.ColumnNames(PivotedColumn)), each if Text.Contains(_, "quantity") then Text.Replace(_, "quantity", "2") else Text.Replace(_, "value", "1")),
        ReorderedColumns = Table.ReorderColumns(PivotedColumn, ReorderedColumnsList)
    in
        ReorderedColumns


    Friday, December 29, 2017 11:55 PM
  • My 2 cents: a solution created using only standard menu options.

    3 Queries:

    Table1 imports Table1 and splits the Week column.

    FixedWeek references Table1 and creates fixed week numbers.

    Result references Table1, unpivots, merges with FixedWeek, sorts, merges columns and pivots.

    This is how it is created:
    https://youtu.be/Ngb9mXn3rI0

    These are the resulting queries:

    Table1:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"Week", type number}, {"value", Int64.Type}, {"quantity", Int64.Type}}),
        #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Week", type text}}, "en-US"), "Week", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Week.1", "Week.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Week.1", Int64.Type}, {"Week.2", Int64.Type}})
    in
        #"Changed Type1"

    FixedWeek:

    let
        Source = Table1,
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Week.1", "Week.2"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
        #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Week.2", Order.Ascending}, {"Week.1", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
        #"Added Prefix" = Table.TransformColumns(#"Added Index", {{"Index", each "Week " & Text.From(_, "en-US"), type text}})
    in
        #"Added Prefix"

    Result:

    let
        Source = Table1,
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Material", "Week.1", "Week.2"}, "Attribute", "Value.1"),
        #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns",{"Week.1", "Week.2"},FixedWeek,{"Week.1", "Week.2"},"FixedWeek",JoinKind.LeftOuter),
        #"Expanded FixedWeek" = Table.ExpandTableColumn(#"Merged Queries", "FixedWeek", {"Index"}, {"Index"}),
        #"Sorted Rows" = Table.Sort(#"Expanded FixedWeek",{{"Index", Order.Ascending}, {"Attribute", Order.Descending}}),
        #"Merged Columns" = Table.CombineColumns(#"Sorted Rows",{"Index", "Attribute"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged"),
        #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Week.1", "Week.2"}),
        #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value.1")
    in
        #"Pivoted Column"



    • Edited by MarcelBeug Saturday, December 30, 2017 9:10 AM
    • Marked as answer by aikibart Tuesday, January 9, 2018 9:11 PM
    Saturday, December 30, 2017 9:08 AM
  • Dear Imke, Colin and Marcel

    Thank you so much for taking the time to reply. I learned many things from your replies:

    - the concept of creating a list and then using that list in a join

    - interesting list functions

    - doing a merge without the need to create a separate query first

    - doing everything i wanted without any coding

    Truly appreciated

    Regards

    Bart

    Tuesday, January 9, 2018 9:04 PM