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.

jbwtp's avatar
jbwtp
Memorable Member
3 years ago

Hi Anonymous,

 

Try something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvEPcnJU0lEy1Dcw1DcyMDICso1NDYGkAUgUTMfqRCuFOfo5+6OpM7QAqTGBqjVTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, days = _t, enteral = _t, inhaled = _t, parental = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"days", Int64.Type}, {"enteral", Int64.Type}, {"inhaled", Int64.Type}, {"parental", Int64.Type}}),
    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

Themain idea is:

1. Isolate the rest of the table (filter off VANCO)

2. Modify VANCO to become IVANCO

3. Modify VANCO as required for the output

4. Combine all three tables.

 

Kind regards,

John