DAX formula to repeat text series in a column

[Beagles_T810]

Hello guys,

I wonder if there would be a DAX formula for a conditional column where these 3 items are repeated continuously:

Distributor

Wholesale

Retail

Then the loop continues again with these same values from distributor to retail and on and on as long as a value exists in the cells of another column that I specify.

[OwenAuger]

Hello @Beagles_T810 

Could you show an example, with some sample data in a table?

It sounds like it might be more of a job for Power Query if it's a dynamic number of iterations, but it might be possible with DAX

[Beagles_T810]

Hi Owen,

I have attached a screenshot of the table. Here's the situation: I get data about products sold at Distributor, Wholesale and Retail prices. I have unpivoted the columns with all the products data into a single column in Power Query. I now need another column that lists the Distributor, Wholesale, and Retail in a countinous loop to match the list in my now unpivoted Products table because the unpivoted data now list downward in the same pattern - each Product shows up thrice as Distributor, Wholesale and Retail categories.

Previoulsy I manually edited the headers of the product columns before unpivoting them (and then just split the unpivoted column by delimiter) but this method is tedious. I thought there could be a neater, shorter way to create this extra column? 

[OwenAuger]

Hi again @Beagles_T810 

There is certainly a simpler way to create that column (without manually editing column names), but I would suggest it should still be done in Power Query, not a DAX calculated column.

Could you provide a link to a file containing the table in its original format?

Then I could give you more specific advice.

Just in case it's useful as inspiration, here is the sort of thing I think you might want to do. The below M code starts with a pivoted table. It then retrieves the relevant column names and positions using Table.Schema, and assigns a Type to each column based on position, in a step called Mapping. Then it unpivots the original table and joins the Mapping table and expands Type.

Regards

Owen

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYvBDcAgDAN38RsJHFqgs6Dsv0btqjwuipK7vcHKGi06CiguMb03j+86kMVeHC/ELZa/juiK8/f68cwQj7+O6IoLmS8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Prod A_1" = _t, #"Prod A_2" = _t, #"Prod A_3" = _t, #"Prod B_1" = _t, #"Prod B_2" = _t, #"Prod B_3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Prod A_1", Int64.Type}, {"Prod A_2", Int64.Type}, {"Prod A_3", Int64.Type}, {"Prod B_1", Int64.Type}, {"Prod B_2", Int64.Type}, {"Prod B_3", Int64.Type}}),
    Custom1 = Table.Schema(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(Custom1, each [Name] <> "Date"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Position"}),
    Mapping = Table.AddColumn(#"Removed Other Columns", "Type", each let PosBase0 = [Position] - 1, PosMod = Number.Mod ( PosBase0,3) in
if PosMod = 0 then "Distributor" else if PosMod = 1 then "Wholesale" else "Retail", type text),
    #"Unpivot Original Table" = Table.UnpivotOtherColumns(#"Changed Type",{"Date"},"Product","Price"),
    #"Merged Mapping" = Table.NestedJoin(#"Unpivot Original Table", {"Product"}, Mapping, {"Name"}, "TypeMapping", JoinKind.LeftOuter),
    #"Expanded TypeMapping" = Table.ExpandTableColumn(#"Merged Mapping", "TypeMapping", {"Type"}, {"Type"})
in
    #"Expanded TypeMapping"

[imnewpbiuser568]

i would suggest you to place your input question and demo data and what you expect place it in some form. this would enable others to help you.