Reformat table

[buttercream]

Hello,

How do I transform this table preferably in DAX as calculated table:

Invoice	Date	Has name	Has address
153	1/1/2024	Yes	Yes
154	1/1/2024	Yes	Yes
155	1/1/2024	No	Yes
156	1/2/2024	Yes	No
157	1/2/2024	Yes	No
158	1/2/2024	Yes	Yes
159	1/3/2024	No	No
160	1/3/2024	No	No

Into this:

Date	Test	% Yes
1/1/2024	Has name	66.67%
1/1/2024	Has address	100.00%
1/2/2024	Has name	100.00%
1/2/2024	Has address	33.33%
1/3/2024	Has name	0.00%
1/3/2024	Has address	0.00%

[Ashish_Mathur]

Hi,

Would you be OK with a Power Query and measure solution?  Why do you need a calculated table solution?

[danextian]

Hi @buttercream 

Things like this are better than in the query editor than in DAX but for the sake of whether it is is possible, please see below:

Reformatted = 
VAR _hasname =
    SELECTCOLUMNS (
        Source,
        "Date", Source[Date],
        "Invoice", Source[Invoice],
        "Test", "Has name",
        "Value", Source[Has name]
    )
VAR _hasaddress =
    SELECTCOLUMNS (
        Source,
        "Date", Source[Date],
        "Invoice", Source[Invoice],
        "Test", "Has address",
        "Value", Source[Has address]
    )
VAR _combined =
    UNION ( _hasname, _hasaddress )
VAR _grouped01 =
    GROUPBY (
        _combined,
        [Date],
        [Test],
        [Value],
        "Count", COUNTX ( CURRENTGROUP (), 1 )
    )
VAR _percentageTable =
    ADDCOLUMNS (
        _grouped01,
        "YesCount",
            VAR _test = [Test]
            VAR _date = [Date]
            RETURN
                CALCULATE (
                    SUMX (
                        FILTER ( _grouped01, [Value] = "Yes" && [Test] = _test && [Date] = _date ),
                        [Count]
                    )
                ),
        "TotalCount",
            VAR _test = [Test]
            VAR _date = [Date]
            RETURN
                CALCULATE (
                    SUMX ( FILTER ( _grouped01, [Test] = _test && [Date] = _date ), [Count] )
                )
    )
VAR _finalTable =
    ADDCOLUMNS (
        _percentageTable,
        "Percentage", DIVIDE ( [YesCount], [TotalCount], 0 ) + 0
    )
RETURN
    GROUPBY ( _finalTable, [Test], [Date], [Percentage] )

Source is the name of the table containing the original data format.

[buttercream]

Thanks.

[rajendraongole1 · Accepted solution]

Hi @buttercream -Open Power Query Editor by clicking on Transform Data.
Unpivot Columns

Select the columns Has name and Has address.
Right-click on the selected columns and choose Unpivot Columns.
This will create two new columns: Attribute (with the values "Has name" and "Has address") and Value (with the values "Yes" and "No").
Rename Columns

Rename the Attribute column to Test.
Rename the Value column to % Yes.
Replace "Yes" and "No"

Replace "Yes" with 1 and "No" with 0 in the % Yes column.
Select the % Yes column.
Go to Transform > Replace Values.
Group Data

Select the Date and Test columns.
Go to Home > Group By.
In the Group By window:
Set Group By to Date and Test.
Set Operation to Average on % Yes and name it Average.
Convert Average to Percentage

Change the data type of the Average column to Percentage or multiply by 100 to format as a percentage.
Rename the Average Column

Rename the Average column to % Yes.
Load Data Back

Click Close & Apply to load the transformed data back into Power BI.

advanced M code FYR:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1VtJRMtQ31DcyMDIBMiNTi6FkrA5I2gS/tCmqtF8+iqwZWNYIVTNQDUTWHK+sBTZZhNGWYGljFIthes0McEjGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Invoice = _t, Date = _t, #"Has name" = _t, #"Has address" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice", Int64.Type}, {"Date", type date}, {"Has name", type text}, {"Has address", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Invoice", "Date"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Test"}, {"Value", "% Yes"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Yes","1",Replacer.ReplaceText,{"% Yes"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","No","0",Replacer.ReplaceText,{"% Yes"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"% Yes", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Date", "Test"}, {{"Average", each List.Average([#"% Yes"]), type nullable text}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Grouped Rows",{{"Average", Percentage.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Average", "Yes"}})
in
#"Renamed Columns1"

[CharlotteHernan]

Thank you so much.