locked
Loop in Power Query RRS feed

  • Question

  • Hi,

    I am new to power query and want to loop over all rows of a table to get some conditional data which needs to be stored as I further need to use this stored data to create new rows dynamically. This is needed because I want to create null records for the years not having data for a choice field so that my graph does not break. Please let me know if there is any alternate solution for this.

    Thanks..


    Wednesday, July 4, 2018 11:04 AM

Answers

  • Hi Tanvi,

    The graph that I plotted assumed 0 and not null values. There are two problems with this approach:

    1) Zero represents a value and null represents the absence of a value. Therefore, if the actual representation should be null, then using zero is misleading.
    2) The graph is skewed such that lines are compressed compared to your original graph. The range of values are not close to zero, yet the graph emphasizes the slopes to the x-axis.

    If we use null instead of zero, the graph is the same as your original graph. Since null cannot be represented by any value on the y-axis, the line must discontinue at those null points. Therefore, for null values, your graph is correct, despite how it looks.

    Anyway, I used the following transformations for the plot:

    let
        PreviousStep = ...,
        RemovedColumns = Table.RemoveColumns(PreviousStep,{"Count"}),
        ColumnNames = Table.ColumnNames(RemovedColumns),
        GroupedRows = Table.Group(RemovedColumns, {"YearNWeek"}, {{"Table", each Table.Pivot(_, List.Distinct(_[Tracks]), "Tracks", "Average"), type table}}),
        CombinedTables = Table.Combine(GroupedRows[Table]),
        TrackColumnNames = List.Select(Table.ColumnNames(CombinedTables), each Text.Contains(_, "Track")),
        ReplacedValue = Table.ReplaceValue(CombinedTables, null, 0, Replacer.ReplaceValue, TrackColumnNames),
        UnpivotedColumns = Table.Unpivot(ReplacedValue, TrackColumnNames, "Track", "Average"),
        ChangedType = Table.TransformColumnTypes(UnpivotedColumns,{{"Average", type number}})
    in
        ChangedType
    Replace PreviousStep with the actual step name.

    Monday, July 9, 2018 2:13 PM

All replies

  • Hey Tanvi,

    Could you provide a sample dataset as well as a sample output? Just trying to visualize what you're trying to accomplish. I'm not completely understanding your scenario.

    Wednesday, July 4, 2018 4:22 PM
  • Hi Miguel,

    Below is the format of my table: Now for ex, yearweek-18'17 have records against Track2 but no records against Track1 and Track 3, which is causing the line chart to break in between. I want to add null/0 records for other tracks against each yearNweek, so that the line chart would be connected. Please help.. Let me know whatever possible ways are available to achieve this.

    Tracks Average Count YearNWeek
    Track2 65.71428571 3 18' 09
    Track1 68.57142857 1 18' 09
    Track1 83.57142857 4 18' 11
    Track2 62.38095238 6 18' 11
    Track1 70.47619048 3 18' 13
    Track2 71.42857143 7 18' 13
    Track2 78.0952381 6 18' 15
    Track1 81.42857143 2 18' 15
    Track2 71.42857143 5 18' 17
    Track2 69.28571429 8 18' 19
    Track2 85.55555556 36 18' 21
    Track3 88.57142857 1 18' 23
    Track1 74.28571429 3 18' 23
    Track2 77.81512605 17 18' 23
    Track1 74.28571429 1 18' 25
    Track2 78.21428571 8 18' 25

    Thursday, July 5, 2018 9:32 AM
  • Hi Tanvi,

    You seem to have no issue that Track3 doesn't exist for most weeks. The question therefore is how does one know that it should exist for 18'17?

    To only way to transform the data is to base any null data on the unique values that appear in the Tracks column. So in this case, Track3 would show up as null for weeks where there are no values. Same thing for any other tracks picked up in the column (e.g. Track4, Track5, etc.).

    Are you creating your line charts in Power BI Desktop or in Excel?

    Edit:

    I'm curious to know how series are being plotted in your chart.

    • Edited by Colin Banfield Thursday, July 5, 2018 6:20 PM Added additional question
    Thursday, July 5, 2018 5:41 PM
  • Yes, I am using Power Bi Desktop to plot the line chart. Attached is the screenshot of the series, with Green line as Track1, Black-Track2 and Red-Track3.

    Friday, July 6, 2018 6:09 AM
  • Are you looking for something like the following?

    Friday, July 6, 2018 3:34 PM
  • Hi Colin,

    Yes, I am looking for the graph shared by you. But the issue is that the values are dynamical, and I cannot insert null values manually, so what is the alternative to plot such connected graph based on the data I have provided.

    Thanks..

    Monday, July 9, 2018 5:22 AM
  • Hi Tanvi,

    The graph that I plotted assumed 0 and not null values. There are two problems with this approach:

    1) Zero represents a value and null represents the absence of a value. Therefore, if the actual representation should be null, then using zero is misleading.
    2) The graph is skewed such that lines are compressed compared to your original graph. The range of values are not close to zero, yet the graph emphasizes the slopes to the x-axis.

    If we use null instead of zero, the graph is the same as your original graph. Since null cannot be represented by any value on the y-axis, the line must discontinue at those null points. Therefore, for null values, your graph is correct, despite how it looks.

    Anyway, I used the following transformations for the plot:

    let
        PreviousStep = ...,
        RemovedColumns = Table.RemoveColumns(PreviousStep,{"Count"}),
        ColumnNames = Table.ColumnNames(RemovedColumns),
        GroupedRows = Table.Group(RemovedColumns, {"YearNWeek"}, {{"Table", each Table.Pivot(_, List.Distinct(_[Tracks]), "Tracks", "Average"), type table}}),
        CombinedTables = Table.Combine(GroupedRows[Table]),
        TrackColumnNames = List.Select(Table.ColumnNames(CombinedTables), each Text.Contains(_, "Track")),
        ReplacedValue = Table.ReplaceValue(CombinedTables, null, 0, Replacer.ReplaceValue, TrackColumnNames),
        UnpivotedColumns = Table.Unpivot(ReplacedValue, TrackColumnNames, "Track", "Average"),
        ChangedType = Table.TransformColumnTypes(UnpivotedColumns,{{"Average", type number}})
    in
        ChangedType
    Replace PreviousStep with the actual step name.

    Monday, July 9, 2018 2:13 PM