How to Pivot Columns and Use Static Column Headers?

[schilders]

I have a business object that attempts to pivot a dollar amount into one of serveral categories.  The applied step is as follows:

Table.Pivot(#"Duplicated Column1", List.Distinct(#"Duplicated Column1"[#"CostBucket - Copy"]), "CostBucket - Copy", "NET2", List.Sum)

The data model throws an error on the next step:

= Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"INPATIENT", "Other", "OUTPATIENT", "NEPHROLOGY", "ER", "DIALYSIS"})

The error is thrown because one of the replaced values, Outpatient, does not exist in the previous step.

Question:  How do I pivot all categories in the second step whether they have a dollar amount or not?  In other words, what steps should be taken to ensure that the Pivot function creates all the columns (Inpatient, Outpatient etc.) and only places a value in the column where the source data has a match?  Is this functionality supported in DAX and Power Query?  Or, am I better off structuring the data via my SQL query?

[Ashish_Mathur]

Hi,

Before the Unpivoting step, replace the null with blank.  If this does not help, then share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Anonymous]

Hi @schilders ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.