Hello, How do I transform this table preferably in DAX as calculated table: InvoiceDateHas nameHas address1531/1/2024YesYes1541/1/2024YesYes1551/1/2024NoYes1561/2/2024YesNo1571/2/2024YesNo1581/2/2024YesYes1591/3/2024NoNo1601/3/2024NoNo Into this:DateTest% Yes1/1/2024Has name66.67%1/1/2024Has address100.00%1/2/2024Has name100.00%1/2/2024Has address33.33%1/3/2024Has name0.00%1/3/2024Has address0.00%

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: letSource = 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"

buttercream

Helper II

1 year ago

Solved

Reformat table

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%

Need Help

rajendraongole1
1 year ago
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"

5 Replies

rajendraongole1
Super User
1 year ago
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
  New Member
  1 year ago
  Thank you so much.

danextian

Super User

1 year ago

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.

grouping and summarizing with DAX.pbix26 KB

buttercream
Helper II
1 year ago
Thanks.

Ashish_Mathur
Super User
1 year ago
Hi,

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

Forum Discussion

Reformat table

5 Replies

Related content

Reformat datetime to only date

Reformat Field to Percent

reformat a table using Power QUERY

Reformatting table/data structue

Matrix table