Columns to be displayed only Once

Question

Hi

I have below Data and i want if DocNum repeats multiple times then RoundDif,InvoiceTotal should be displayed only once

Invoice No	Quantity	Basic Value	Tax Amount	Round Diff	Invoice Total
500273	513	18,940.00	2,272.80	-0.27	23,529.00
500273	309	1,963.11	353.36	-0.27	23,529.00
500286	965	3,840.55	460.87	-0.42	4,301.00

Thanks

Tuhin Paul · Answer

Alternative Approach Using DAX If you prefer to handle this in Power BI using DAX, you can create calculated columns to achieve the same result. DAX Code Add an Index column in Power Query (as described above). Create the following DAX measures: Round Diff Display = IF( MIN('Table'[Index]) = CALCULATE(MIN('Table'[Index]), ALLEXCEPT('Table', 'Table'[Invoice No])), MAX('Table'[Round Diff]), BLANK() ) Invoice Total Display = IF( MIN('Table'[Index]) = CALCULATE(MIN('Table'[Index]), ALLEXCEPT('Table', 'Table'[Invoice No])), MAX('Table'[Invoice Total]), BLANK() ) Replace the original Round Diff and Invoice Total columns with these calculated columns in your visualizations.

Tuhin Paul · Answer

Final Output After applying the above steps, your data will look like this: Invoice No Quantity Basic Value Tax Amount Round Diff Invoice Total 500273 513 18,940.00 2,272.80 -0.27 23,529.00 500273 309 1,963.11 353.36 500286 965 3,840.55 460.87 -0.42 4,301.00

Tuhin Paul · Answer

Step 1: Load Data into Power Query Copy your data into Excel or Power BI. In Power BI: Go to the Home tab and click Transform Data to open Power Query Editor. In Excel: Select the data range and go to the Data tab, then click Get & Transform Data > From Table/Range . Step 2: Add an Index Column Adding an index column helps identify the first occurrence of each Invoice No . In Power Query Editor, go to the Add Column tab. Click Index Column > From 0 (or From 1, depending on your preference). Your data will now have an additional column called Index . Step 3: Identify First Occurrence of Each Invoice No Group the data by Invoice No to find the minimum Index for each Invoice No . Go to the Home tab and click Group By . Configure the grouping as follows: Group by : Invoice No New column name : MinIndex Operation : Minimum Column : Index Expand the grouped data back to the original table: Click the expand icon (two arrows) next to the new grouped column. Select all columns except MinIndex to bring back the original data. Step 4: Add Conditional Columns for Round Diff and Invoice Total Now, create conditional logic to display Round Diff and Invoice Total only for the first occurrence of each Invoice No . Go to the Add Column tab and click Conditional Column . For Round Diff : Create a new column called Round Diff Display . Set the condition: If Index equals MinIndex , then display Round Diff . Otherwise, leave it blank. Repeat the same process for Invoice Total : Create a new column called Invoice Total Display . Set the condition: If Index equals MinIndex , then display Invoice Total . Otherwise, leave it blank. Step 5: Remove Unnecessary Columns Remove the original Round Diff and Invoice Total columns if they are no longer needed. Optionally, rename the new conditional columns ( Round Diff Display and Invoice Total Display ) back to Round Diff and Invoice Total . Step 6: Load Data Back Once the transformation is complete, click Close & Apply in Power Query Editor. The transformed data will now be loaded into Power BI or Excel.

Tuhin Paul · Answer

To achieve the desired output where Round Diff and Invoice Total are displayed only once for repeated Invoice No , you can use Power Query in Power BI or Excel to transform the data. Desired Output For your example data, the transformed table should look like this: Invoice No Quantity Basic Value Tax Amount Round Diff Invoice Total 500273 513 18,940.00 2,272.80 -0.27 23,529.00 500273 309 1,963.11 353.36 500286 965 3,840.55 460.87 -0.42 4,301.00

Eliana Blake · Answer

In the data provided, you mentioned that if DocNum repeats multiple times, then RoundDif and InvoiceTotal should be displayed only once. This approach is commonly used to summarize the RoundDif and InvoiceTotal values when there are multiple entries for the same document number. For example, in the table you shared: - For Invoice No 500273, the Round Difference is -0.27 and Invoice Total is 23,529.00. These values are consistent across both entries with the same Invoice No. - Similarly, for Invoice No 500286, the Round Difference is -0.42 and the Invoice Total is 4,301.00. By displaying RoundDif and InvoiceTotal only once for each unique DocNum, you can provide a more concise and summarized view of the data while still capturing the essential information associated with each document. This approach is helpful in scenarios where you want to avoid redundant information and focus on key metrics or totals associated with each document or transaction. It streamlines the presentation of data, making it easier to interpret and analyze, especially when dealing with large datasets or repetitive entries. If you have any more questions or need further clarification on this topic, feel free to ask!