Create duplicate rows with alternating column values.

[Anonymous]

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 looks now (excerpt, full set includes more drugs, locations, dates): 

Here is what it needs to look like:

1. The Vanco row needs to be split into a new IV Vanco row (Tobra and other rows should not be changed).

2. The Vanco row should only keep the enteral count column value, with 0 filled in for the parenteral

3. The IV vanco row should only keep the parenteral count column value, with 0 filled in for the enteral.

4. Number of days present should be adjusted accordingly to sum the other 3 columns.

5. Summary month and location must be duplicated since there are many other locations and months in the whole data set.

Ive been fully stumped on how to accomplish all these steps.

Thank you!!

[AlexisOlson · Accepted solution]

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]

Hi all,

This is an elegant method, but in my experiance pivoting/unpivoting is quite costly operation and can take a lot of time for large tables. If this is not a problem in this case (e.g. the size of the table is less than 100 of rows), this is great. Otherwise, it may require some testing to optimise the performance.

Cheers,

John

[Anonymous]

Hi Alexis, I think this is close to the solution. I'm very new to power query so I appreciate the help here.

I've taken your code and tried to add it to the end of my own, but I dont appear to be doing the translation correctly.

I'll also note that I only want to do the IV change for Vancomycin. Other drugs have to be left alone.

Here's what ive ended up with, but it doesnt give me any change to the drug names. It just doesnt make the change (for parenteral count rows only), for VANC to IV VANC.

Source and other code above this line

 #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Parenteral Route", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"digestive_Count", "Enteral 
 Count"}, {"respiratory_Count", "Inhaled Count"}, {"Parenteral Route", "Parenteral Count"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"IM_Count", "IV_Count"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"orgID", 
 "drugIngredientDesc", "summaryYM", "location", "antimicrobialDays", "numDaysPresent"}, 
 "Attribute", "Value"),#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns",each 
 [drugIngredientDesc], each if [Method] = "Parenteral" & [drugIngredientDesc] = "VANC - Vancomysin" then "IV " & [drugIngredientDesc] else [drugIngredientDesc], Replacer.ReplaceText, 
 {"drugIngredientDesc"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each true)
   in 
#"Filtered Rows"

Thanks again for reading.

[AlexisOlson]

Try using "and" instead of "&". The latter is used for concatenating strings together, not as a logical operator.

[Anonymous]

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]

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

[jbwtp]

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