Forum Discussion
Create duplicate rows with alternating column values.
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.
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.
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.
Try using "and" instead of "&". The latter is used for concatenating strings together, not as a logical operator.
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
