Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create duplicate rows with alternating column values.

Hello! Im stuck on a complex power query step and would love some ideas on how to proceed. I need to split some of my rows into new rows, duplicating some of the values.   Here is how the data loo...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    If I'm understanding the broader context of your question correctly, then I think a different approach is worth considering. The key is unpivoting your count columns. If you unpivot those columns, you'll get a row for each method.

     

    Before:

    After:

    Then you can rename the drug based on the method. You can pivot again at the end if you want, but it might be better to keep the rows unpivoted (it may make writing DAX measures easier).

     

    Here's the example above you can paste into the Advanced Editor of a new Blank Query to look at the steps in more detail:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvF3CnJU0lEy1DfUNzIwMgQygzwcnYCUAUgUTMfqRCuFOfo5+2NVZwJVawZW5xjlGeIRBFJphNVEIyA2Bqt09vH0cwFZbYyh0BChMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Drug = _t, Date = _t, Location = _t, Enteral = _t, Inhaled = _t, Parenteral = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Drug", type text}, {"Date", type date}, {"Enteral", Int64.Type}, {"Inhaled", Int64.Type}, {"Parenteral", Int64.Type}}),
    #"Unpivoted Columns" = Table.Unpivot(#"Changed Type", {"Enteral", "Inhaled", "Parenteral"}, "Method", "Count"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Count] <> 0)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",each [Drug], each if [Method] = "Parenteral" then "IV " & [Drug] else [Drug], Replacer.ReplaceText, {"Drug"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Method]), "Method", "Count", List.Sum)
in
    #"Pivoted Column"

    Final output:

     To use this on your own data, replace my first two steps (Source and #"Changed Type") with whatever your data source is, update the table reference in #"Unpivoted Columns" to match whatever the name of the last step in your query is, and update the actual column names in your query or the column name references in my new part of the query to match.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks for replying John. 

Is there a way to use this code added on to the end of my other powerBI query code?

With the way your code is set up now, it has its own source statement at the beginning.

I need to add the code to the end of the rest of my query, I may have not been clear enough in my question.

Thank you!

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    Hi Anonymous,

     

    If you can share you code (at least the last couple of lines, I can show how to stitch it together.

    But the idea is that you would append this bit of my code:

        TheRestOfTheTable = Table.SelectRows(#"Changed Type", each ([Name] <> "VANCO")),
    VANCO_Only = Table.SelectRows(#"Changed Type", each ([Name] = "VANCO")),
    KeepRequiredColumns = Table.SelectColumns(VANCO_Only,{"Name", "Date", "days", "parental"}),
    SetUpIVANCO = Table.ReplaceValue(KeepRequiredColumns,"VANCO","IVANCO",Replacer.ReplaceText,{"Name"}),
    ResetParental = Table.ReplaceValue(VANCO_Only, null, null, (x,y,z) as number => 0,{"parental"}),
    Combine = Table.Combine({TheRestOfTheTable, ResetParental, SetUpIVANCO})
in
    Combine

    to the end of your code (replacing whatever in ...  you have a the end of your code and adding a comma to the last line of your code [before in]). You will also need to fix the column names in my code as I simplified it compared to the original table.

     

    Kind regards,

    John